Replace OUTER APPLY and CROSS APPLY with LEFT JOINs for Snowflake

By: Sergey Gigoyan | Updated: 2023-07-21 | Comments | Related: More > Snowflake

Problem

When converting Microsoft SQL Server code to Snowflake, some features in SQL Server are not available in Snowflake. The APPLY operator is one such example. This article will discuss several examples of converting SQL code with the OUTER APPLY and CROSS APPLY operators to the equivalent Snowflake SQL.

Solution

We will discuss several use cases of OUTER APPLY and CROSS APPLY and demonstrate how to convert these pieces of code from SQL Server to Snowflake. The majority of examples are taken from a real case scenario.

In the APPLY operator, the right-side table of the operator is evaluated for each row of the left-side table. This can be useful when the right table contains a table-valued function that uses column values of the left table as arguments. You need to include either CROSS or OUTER to use the APPLY function. If you choose CROSS, no rows will be generated when the right table source is compared to a specific row of the left table source and doesn't match. On the other hand, if you select OUTER, a row will be produced for every row of the left table source, even if the right table source is compared to that row and doesn't return any results.

OUTER APPLY

The OUTER APPLY can mostly be converted by using LEFT JOINS. We will rewrite the pieces of code with OUTER APPLY using LEFT JOINS in SQL to get rid of OUTER APPLYs and then provide the Snowflake-converted version.

In the SQL Server code below, global temporary tables are created to perform some tests with OUTER APPLY:

-- source: https://www.MSSQLTips.com

--TableA
IF OBJECT_ID(N'tempdb..##TableA') IS NOT NULL  
   DROP TABLE ##TableA  
 
CREATE TABLE ##TableA(ID INT, Val INT)
 
INSERT INTO ##TableA (ID, Val) VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50)
 
--TableB
IF OBJECT_ID(N'tempdb..##TableB') IS NOT NULL  
   DROP TABLE ##TableB 
   
CREATE TABLE ##TableB(ID INT, Val INT)
 
INSERT INTO ##TableB (ID, Val) VALUES (1, 100), (3, 300), (5, 500), (5, 550), (7, 700)

Now, let's introduce some examples of using OUTER APPLY and their analogs without it. In each example below, there is a simple code with the OUTER APPLY and the corresponding code rewritten using LEFT JOINs:

Simple OUTER APPLY

This is a simple OUTER APPLY compared to using a LEFT JOIN.

-- source: https://www.MSSQLTips.com

--1. Simple OUTER APPLY
--OUTER APPLY
SELECT *
FROM 
##TableA a
OUTER APPLY (SELECT * FROM ##TableB WHERE a.ID=ID) b
 

--LEFT JOIN
SELECT *
FROM 
##TableA a
LEFT JOIN
##TableB b
ON a.ID =b.ID

As we can see, the results are the same:

OUTER APPLY with TOP

This is an example of using TOP with OUTER APPLY compared to using a LEFT JOIN.

-- source: https://www.MSSQLTips.com

--2. OUTER APPLY with TOP
 
--OUTER APPLY
SELECT *
FROM 
##TableA a
OUTER APPLY (SELECT TOP 1 Val as b_val FROM ##TableB WHERE ID=a.ID ORDER BY Val) b
 

--LEFT JOIN
SELECT a.*, b.Val as b_Val
FROM 
##TableA a
LEFT JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val) as row_num
 FROM 
 ##TableB bb
 INNER JOIN 
 ##TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1

OUTER APPLY with Aggregation

This is an example of aggregated values for an OUTER APPLY query compared to using a LEFT JOIN.

-- source: https://www.MSSQLTips.com

--3. OUTER APPLY with aggregation
 
--OUTER APPLY
SELECT *
FROM 
##TableA a
OUTER APPLY (SELECT MAX(Val) as b_MaxVal FROM ##TableB WHERE ID=a.ID) b
 

--LEFT JOIN
SELECT a.*, b.Val as b_MaxVal
FROM 
##TableA a
LEFT JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val DESC) as row_num
 FROM 
 ##TableB bb
 INNER JOIN 
 ##TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1

OUTER APPLY with One Table

This is an example of using OUTER APPLY with a CASE statement compared to using a LEFT JOIN.

-- source: https://www.MSSQLTips.com

--4. OUTER APPLY with one table
 
--OUTER APPLY
SELECT *
FROM 
##TableA a
OUTER APPLY 
(
    SELECT CASE WHEN Val<40 THEN Val 
                ELSE NULL 
             END AS Std,
          CASE WHEN Val>=40 THEN Val 
                ELSE NULL 
             END AS Prm
) v

 
--Converted - using one SELECT with CASE 
SELECT      a.*, 
         CASE WHEN Val<40 THEN Val 
                ELSE NULL 
             END AS Std,
          CASE WHEN Val>=40 THEN Val 
                ELSE NULL 
             END AS Prm
 
FROM 
##TableA a

Converted OUTER APPLY Code for Snowflake

Thus, as we already know how to replace OUTER APPLY with left joins in the pieces of code above, we can easily convert it to Snowflake.

The converted code in Snowflake is:

-- source: https://www.MSSQLTips.com

--Temporary tables
--TableA
CREATE OR REPLACE TEMPORARY TABLE TableA(ID INT, Val INT);
 
INSERT INTO TableA (ID, Val) VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50);
 
--TableB
CREATE OR REPLACE TEMPORARY TABLE TableB(ID INT, Val INT);
 
INSERT INTO TableB (ID, Val) VALUES (1, 100), (3, 300), (5, 500), (5, 550), (7, 700);
 

--1. Converted OUTER APPLY to LEFT JOIN - Snowflake 
 
--LEFT JOIN
SELECT *
FROM 
TableA a
LEFT JOIN
TableB b
ON a.ID =b.ID;
 
 
--2. Converted OUTER APPLY with TOP - Snowflake
 
--LEFT JOIN
SELECT a.*, b.Val as b_Val
FROM 
TableA a
LEFT JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val) as row_num
 FROM 
 TableB bb
 INNER JOIN 
 TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1;
 
 
--3. Converted OUTER APPLY with aggregation - Snowflake
 
--LEFT JOIN
SELECT a.*, b.Val as b_MaxVal
FROM 
TableA a
LEFT JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val DESC) as row_num
 FROM 
 TableB bb
 INNER JOIN 
 TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1;


--4. Converted OUTER APPLY with one table - Snowflake
 
--Converted - using one SELECT with CASE 
SELECT      a.*, 
         CASE WHEN Val<40 THEN Val 
                ELSE NULL 
             END AS Std,
          CASE WHEN Val>=40 THEN Val 
                ELSE NULL 
             END AS Prm
 
FROM 
TableA a;

CROSS APPLY

The CROSS APPLY operator can be converted using INNER JOINS in most cases. However, in the third example (CROSS APPLY with aggregation), we consider two examples for conversion. Depending on the CROSS APPLY logic, it can be converted in different ways. In that example, when the GROUP BY clause is not used in aggregation, the result of the CROSS APPLY is the same as OUTER APPLY as the MAX() function still returns a row (NULL) if the condition does not match. In contrast, when GROUP BY is used, INNER JOIN should be used to convert.

-- source: https://www.MSSQLTips.com

--TableA
IF OBJECT_ID(N'tempdb..##TableA') IS NOT NULL  
   DROP TABLE ##TableA  
 
CREATE TABLE ##TableA(ID INT, Val INT)
 
INSERT INTO ##TableA (ID, Val) VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50)
 
--TableB
IF OBJECT_ID(N'tempdb..##TableB') IS NOT NULL  
   DROP TABLE ##TableB  
 
CREATE TABLE ##TableB(ID INT, Val INT)
 
INSERT INTO ##TableB (ID, Val) VALUES (1, 100), (3, 300), (5, 500), (5, 550), (7, 700)
 

--1. Simple CROSS APPLY
--CROSS APPLY
SELECT *
FROM 
##TableA a
CROSS APPLY (SELECT * FROM ##TableB  WHERE a.ID=ID) b
 
--INNER JOIN
SELECT *
FROM 
##TableA a
INNER JOIN
##TableB b
ON a.ID =b.ID
 
 
--2. CROSS APPLY with TOP
 
--CROSS APPLY
SELECT *
FROM 
##TableA a
CROSS APPLY (SELECT TOP 1 Val as b_val FROM ##TableB WHERE ID=a.ID ORDER BY Val) b
 
--INNER JOIN
SELECT a.*, b.Val as b_Val
FROM 
##TableA a
INNER JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val) as row_num
 FROM 
 ##TableB bb
 INNER JOIN 
 ##TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1
 
 
--3. CROSS APPLY with aggregation
 
--CROSS APPLY
SELECT *
FROM 
##TableA a
CROSS APPLY (SELECT MAX(Val) as b_MaxVal FROM ##TableB WHERE ID=a.ID GROUP BY ID) b
 
--INNER JOIN
SELECT a.*, b.Val as b_MaxVal
FROM 
##TableA a
INNER JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val DESC) as row_num
 FROM 
 ##TableB bb
 INNER JOIN 
 ##TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1
 
--Without GROUP BY - the same as OUTER APPLY because MAX returns NULL when there is no row to select
SELECT *
FROM 
##TableA a
CROSS APPLY (SELECT MAX(Val) as b_MaxVal FROM ##TableB WHERE ID=a.ID) b
 
--LEFT  join
SELECT a.*, b.Val as b_MaxVal
FROM 
##TableA a
LEFT JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val DESC) as row_num
 FROM 
 ##TableB bb
 INNER JOIN 
 ##TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1
 

--4. CROSS APPLY with one table
 
--CROSS APPLY
SELECT *
FROM 
##TableA a
CROSS APPLY 
(
    SELECT CASE WHEN Val<40 THEN Val 
                ELSE NULL 
             END AS Std,
          CASE WHEN Val>=40 THEN Val 
                ELSE NULL 
             END AS Prm
) v
 
--Converted - using one SELECT with CASE 
SELECT      a.*, 
         CASE WHEN Val<40 THEN Val 
                ELSE NULL 
             END AS Std,
          CASE WHEN Val>=40 THEN Val 
                ELSE NULL 
             END AS Prm
 
FROM 
##TableA a

Converted CROSS APPLY Code in Snowflake

Now, let's translate the SQL Server code above into Snowflake SQL:

-- source: https://www.MSSQLTips.com

--Temporary tables
--TableA
CREATE OR REPLACE TEMPORARY TABLE TableA(ID INT, Val INT);
 
INSERT INTO TableA (ID, Val) VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50);
 
--TableB
CREATE OR REPLACE TEMPORARY TABLE TableB(ID INT, Val INT);
 
INSERT INTO TableB (ID, Val) VALUES (1, 100), (3, 300), (5, 500), (5, 550), (7, 700);
 

--1. Converted simple CROSS APPLY to INNER JOIN - Snowflake
 
--INNER JOIN
SELECT *
FROM 
TableA a
INNER JOIN
TableB b
ON a.ID =b.ID;
 
 
--2. Converted CROSS APPLY with TOP - Snowflake
 
--INNER JOIN
SELECT a.*, b.Val as b_Val
FROM 
TableA a
INNER JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val) as row_num
 FROM 
 TableB bb
 INNER JOIN 
 TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1;
 
 
--3. Converted CROSS APPLY with aggregation - Snowflake
 
--INNER JOIN - when GROUP BY is used in CROSS APPLY (SELECT MAX(Val) as b_MaxVal, ID FROM TableB WHERE ID=a.ID GROUP BY ID)
SELECT a.*, b.Val as b_MaxVal
FROM 
TableA a
INNER JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val DESC) as row_num
 FROM 
 TableB bb
 INNER JOIN 
 TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1;
 
--Without GROUP BY in CROSS APPLY - the same as OUTER APPLY because MAX returns NULL when there is no row to select (SELECT MAX(Val) as b_MaxVal FROM TableB WHERE ID=a.ID)
 
--LEFT join 
SELECT a.*, b.Val as b_MaxVal
FROM 
TableA a
LEFT JOIN
(SELECT bb.*, ROW_NUMBER() OVER (PARTITION BY bb.ID ORDER BY bb.val DESC) as row_num
 FROM 
 TableB bb
 INNER JOIN 
 TableA aa
 ON aa.ID=bb.ID) b
ON a.ID =b.ID AND b.row_num=1;
 

--4. Converted CROSS APPLY with one table - Snowflake
 
--Converted - using one SELECT with CASE 
SELECT      a.*, 
         CASE WHEN Val<40 THEN Val 
                ELSE NULL 
             END AS Std,
          CASE WHEN Val>=40 THEN Val 
                ELSE NULL 
             END AS Prm
 
FROM 
TableA a;

If we run this code in Snowflake, we can see that the results are identical to the corresponding query results in SQL Server.

Conclusion

In conclusion, OUTER APPLY and CROSS APPLY operators can be rewritten to Snowflake SQL, primarily using LEFT JOINs and INNER JOINs. Understanding the logic behind the code and converting it accordingly is essential.

Next Steps

For more information, please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-07-21

Convert OUTER APPLY and CROSS APPLY Joins for Snowflake using LEFT JOINS