OUTER APPLY

Outer apply statement equivalence translation.

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 of 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

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

Output

p.ProjectName	e.ProjectName	FirstName
Project A	Project A	John
Project A	Project A	Jane
Project A	Project B	Michael
Project B	Project A	John
Project B	Project A	Jane
Project B	Project B	Michael
Project C	Project A	John
Project C	Project A	Jane
Project C	Project B	Michael

Snowflake

Snowflake

SELECT
    p.ProjectName,
    e.ProjectName,
    e.FirstName
FROM
    Project p
    INNER JOIN
        LATERAL (
               SELECT
                   ProjectName,
                   FirstName,
                   LastName
               FROM
                   Employees e
           ) e /*** MSC-WARNING - MSCEWI4030 - Equivalence from OUTER APPLY to INNER JOIN LATERAL must be checked. ***/;

Output

PROJECTNAME	PROJECTNAME_2	FIRSTNAME
Project A	Project A	John
Project A	Project A	Jane
Project A	Project B	Michael
Project B	Project A	John
Project B	Project A	Jane
Project B	Project B	Michael
Project C	Project A	John
Project C	Project A	Jane
Project C	Project B	Michael

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 problems 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

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

SELECT
            UNIVERSAL_NAME
FROM
            SAMPLE_ATLAS
            AS SATT
            OUTER APPLY
                        /*** MSC-ERROR - MSCCP0001 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/ (SELECT TOP 1
                                                UNIVERSAL_NAME,
                                                INTERNATIONAL_NAME,
                                                CODE_IDENTIFIER
                                    FROM
                                                SAMPLE_GLOBE AS SG
                                    WHERE
                                                SG.GLOBE_KEY = SATT.MbrPersGenKey
                                    ORDER BY GLOBE_KEY
                        );

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

SQL Server

SELECT	familyName,
	c.givenName AS childGivenName,
	c.firstName AS childFirstName,
	p.givenName AS petName 
FROM Families f 
	CROSS APPLY OPENJSON(f.doc) 
		WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
		CROSS APPLY OPENJSON(children) 
		WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
			OUTER APPLY OPENJSON (pets)
			WITH (givenName nvarchar(100))  as p

Translation output

SELECT
	familyName,
	c.givenName AS childGivenName,
	c.firstName AS childFirstName,
	p.givenName AS petName
FROM
	Families f
	LEFT OUTER JOIN
		OPENJSON(f.doc) /*** MSC-WARNING - MSCEWI4030 - Equivalence from CROSS APPLY to LEFT OUTER JOIN must be checked. ***/;
-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 7 OF THE SOURCE CODE. **
--		WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
--		CROSS APPLY OPENJSON(children)
--		WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
--			OUTER APPLY OPENJSON (pets)
--			WITH (givenName nvarchar(100))  as p

Related EWIs

MSCEWI4030: Warning while OUTER/CROSS APPLY equivalence is checked.