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:
