OUTER APPLY

Outer apply statement equivalence translation.

Some parts in the output code are omitted for clarity reasons.

Description

When OUTER APPLY is specified, one row is produced for each row of the left rowset even when the right-side rowset expression returns an empty rowset for that row. (OUTER APPLY Definition)

Syntax

  Apply_Operator :=                                                                                   
       'CROSS' 'APPLY'
  |    'OUTER' 'APPLY'.

Snowflake equivalence

Despite the unsupported statement OUTER APPLY in Snowflake, there is an equivalent statement which is LATERAL. Hence, the translation for the statement is conducted to get the same functionality through the use of alternative solutions.

Nevertheless, the LATERAL statement in Snowflake has two variations in syntax. In fact, the INNER JOIN LATERAL variation is used in this specific translation.

The INNER JOIN LATERAL grammar from Snowflake is the following:

SELECT ...
FROM <left_hand_table_expression> INNER JOIN LATERAL ( <inline_view> )
...

<inline_view> must not be a table name.

And, the single LATERAL statement is shown below:

SELECT ...
FROM <left_hand_table_expression>, LATERAL ( <inline_view> )
...

Sample source

The following example shows a general translation between OUTER APPLY and INNER JOIN LATERAL:

SQL Server

SQL Server

IN -> SqlServer_01.sql

SELECT
    p.ProjectName,
    e.ProjectName,
    e.FirstName
FROM
    Project p
    OUTER APPLY (
        SELECT
            ProjectName,
            FirstName,
            LastName
        FROM
            Employees e
    ) e;

Output

Snowflake

Snowflake

OUT -> SqlServer_01.sql

SELECT
    p.ProjectName,
    e.ProjectName,
    e.FirstName
FROM
    Project p
    INNER JOIN
        LATERAL (
                   SELECT
                       ProjectName,
                       FirstName,
                       LastName
                   FROM
                       Employees e
               ) e;

Output

Known issues

Since the translation is an equivalence from the input, there are some limitations.

• TOP and WHERE statements may be reviewed for optimal behavior.

• A correlation name at the end of the statement may be needed. In Snowflake, the query does not represent a problem if the correlation name is not in the query, but functionality may change and does not form part of the accepted pattern in SQL Server.

SQL Server

IN -> SqlServer_02.sql

SELECT
    SATT.UNIVERSAL_NAME
FROM
SAMPLE_ATLAS AS SATT
OUTER APPLY (
    SELECT
        TOP 1 UNIVERSAL_NAME,
        INTERNATIONAL_NAME,
        CODE_IDENTIFIER
    FROM
        SAMPLE_GLOBE AS SG
    WHERE
        SG.GLOBE_KEY = SATT.MbrPersGenKey
    ORDER BY
        GLOBE_KEY
);

Translation output

• Specific statements that are not supported may comment out all the block code (example taken from: JSON Example).

SQL Server

OUT -> SqlServer_02.sql

SELECT
    SATT.UNIVERSAL_NAME
FROM
SAMPLE_ATLAS AS SATT
INNER JOIN LATERAL (
    SELECT
        TOP 1 UNIVERSAL_NAME,
        INTERNATIONAL_NAME,
        CODE_IDENTIFIER
    FROM
        SAMPLE_GLOBE AS SG
    WHERE
        SG.GLOBE_KEY = SATT.MbrPersGenKey
    ORDER BY
        GLOBE_KEY
);

Translation output

Related EWIs

1. SSC-EWI-TS0030: Warning while OUTER JOIN equivalence is checked.