Common Table Expression (CTE)

Common table expressions are supported in Snowflake SQL by default.

Snowflake SQL syntax

Subquery:

[ WITH
       <cte_name1> [ ( <cte_column_list> ) ] AS ( SELECT ...  )
   [ , <cte_name2> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
   [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
]
SELECT ...

Recursive CTE:

[ WITH [ RECURSIVE ]
       <cte_name1> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause )
   [ , <cte_name2> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
   [ , <cte_nameN> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
]
SELECT ...

Where:

anchorClause ::=
    SELECT <anchor_column_list> FROM ...

recursiveClause ::=
    SELECT <recursive_column_list> FROM ... [ JOIN ... ]

Noteworthy details

The RECURSIVE keyword does not exist in T-SQL, and the transformation does not actively add the keyword to the result. A warning is added to the output code in order to state this behavior.

Common Table Expression with SELECT INTO

The following transformation occurs when the WITH expression is followed by an SELECT INTO statement and it will be transformed into a TEMPORARY TABLE.

SQL Server:

WITH ctetable(col1, col2) AS
    (
        SELECT	col1, col2 FROM	t1 poh WHERE poh.col1 = 16 and poh.col2 = 4
    ),
    employeeCte AS
    (
	SELECT BUSINESSENTITYID, VACATIONHOURS FROM employee WHERE BUSINESSENTITYID = (SELECT col1 FROM ctetable)
    ),
    finalCte AS
    (
        SELECT BUSINESSENTITYID, VACATIONHOURS FROM employeeCte  
    ) SELECT * INTO #table2 FROM finalCte;

SELECT * FROM #table2;

Snowflake:

CREATE OR REPLACE TEMPORARY TABLE table2 AS
    WITH ctetable(col1, col2) AS
    (
        SELECT	col1, col2 FROM	t1 poh WHERE poh.col1 = 16 and poh.col2 = 4
    ),
    employeeCte AS
    (
	SELECT BUSINESSENTITYID, VACATIONHOURS FROM employee WHERE BUSINESSENTITYID = (SELECT col1 FROM ctetable)
    ),
    finalCte AS
    (
        SELECT BUSINESSENTITYID, VACATIONHOURS FROM employeeCte  
    ) SELECT * FROM finalCte;
    
SELECT * FROM table2;

Common Table Expression with other expressions

The following transformation occurs when the WITH expression is followed by INSERT or DELETE statements. In this case, it only keeps the SELECT statement in the WITH clause and the table used in the WITH clause is replaced with TABLE(RESULT_SCAN(LAST_QUERY_ID()))

SQL Server:

WITH CTE AS( SELECT * from table1)
INSERT INTO Table2 (a,b,c,d)
SELECT a,b,c,d
FROM CTE
WHERE e IS NOT NULL;

Snowflake:

/*** MSC-WARNING - MSCEWI4020 - COMMON TABLE EXPRESSION IS NOT SUPPORTED FOR INSERT STATEMENT. TABLE(RESULT_SCAN(LAST_QUERY_ID())) WAS USED INSTEAD IN ORDER TO PRESERVE THE CURRENT BEHAVOUR ***/
SELECT * from PUBLIC.table1;

INSERT INTO PUBLIC.Table2 (a, b, c, d)
SELECT a,
b,
c,
d
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS CTE
WHERE e IS NOT NULL;

Common Table Expression with Delete From

For this transformation, it will only apply for a CTE (Common Table Expression) with a Delete From, however, only for some specifics CTE. It must have only one CTE, and it must have inside a function of ROW_NUMBER or RANK.

The purpose of the CTE with the Delete must be to remove duplicates from a table. In case that the CTE with Delete intents to remove another kind of data, this transformation will not apply.

Let's see an example. For a working example, we must first create a table with some data.

CREATE TABLE WithQueryTest
(
    ID BIGINT,
    Value BIGINT,
    StringValue NVARCHAR(258)
);

Insert into WithQueryTest values(100, 100, 'First');
Insert into WithQueryTest values(200, 200, 'Second');
Insert into WithQueryTest values(300, 300, 'Third');
Insert into WithQueryTest values(400, 400, 'Fourth');
Insert into WithQueryTest values(100, 100, 'First');

Note that there is a duplicated value. The lines 8 and 12 insert the same value. Now we are going to eliminate the duplicates rows in a table.

WITH Duplicated AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RN
FROM WithQueryTest
)
DELETE FROM Duplicated
WHERE Duplicated.RN > 1

If we execute a Select from the table, it will show the following result

Note that there are no duplicateds rows. In order to conserve the functionality of these CTE with Delete in Snowflake, it will be transformed to

/*** MSC-WARNING - MSCEWI4026 - WITH DELETE QUERY TURNED TO CREATE TABLE ***/
CREATE OR REPLACE TABLE PUBLIC.WithQueryTest AS SELECT
*
FROM PUBLIC.WithQueryTest
QUALIFY ROW_NUMBER()
OVER (PARTITION BY ID ORDER BY ID) = 1 ;

As you can see, the query is transformed to a Create Or Replace Table.

Let's try it in Snowflake, in order to test it, we need the table too.

CREATE OR REPLACE TABLE PUBLIC.WithQueryTest
(
ID BIGINT,
Value BIGINT,
StringValue VARCHAR(258)
);

Insert into PUBLIC.WithQueryTest values(100, 100, 'First');
Insert into PUBLIC.WithQueryTest values(200, 200, 'Second');
Insert into PUBLIC.WithQueryTest values(300, 300, 'Third');
Insert into PUBLIC.WithQueryTest values(400, 400, 'Fourth');
Insert into PUBLIC.WithQueryTest values(100, 100, 'First');

Now, if we execute the result of the transformation, and then a Select to check if the duplicated rows were deleted, this would be the result.

Common Table Expression with MERGE statement

The following transformation occurs when the WITH expression is followed by MERGE statement and it will be transformed into a MERGE INTO.

SQL Server:

WITH ctetable(col1, col2) as 
    (
        SELECT col1, col2
        FROM t1 poh
        where poh.col1 = 16 and poh.col2 = 88
    ),
    finalCte As
    (
        SELECT col1 FROM ctetable  
    )  
    MERGE  
  table1 AS target
  USING finalCte AS source  
  ON (target.ID = source.COL1)
  WHEN MATCHED THEN UPDATE SET target.ID = source.Col1
  WHEN NOT MATCHED THEN INSERT (ID, col1) VALUES (source.COL1, source.COL1 );

Snowflake:

MERGE INTO MYDB.PUBLIC.table1 AS target
  USING 
  (
        WITH ctetable 
        (
              col1,
              col2
        ) AS
           (
               SELECT
                    col1,
                    col2
                    FROM
                    MYDB.PUBLIC.t1 poh
                    WHERE
                    poh.col1 = 16 and poh.col2 = 88
           ),
        finalCte As
        (
                SELECT
                    col1
                    FROM
                    MYDB.PUBLIC.ctetable
        )
        SELECT
              *
              FROM
              finalCte
  ) AS source ON (target.ID = source.COL1)
  WHEN MATCHED THEN
        UPDATE SET target.ID = source.Col1
  WHEN NOT MATCHED THEN
        INSERT (ID, col1) VALUES (source.COL1, source.COL1);

Common Table Expression with UPDATE statement

The following transformation occurs when the WITH expression is followed by an UPDATE statement and it will be transformed into an UPDATE statement.

SQL Server:

WITH ctetable(col1, col2) AS 
    (
        SELECT col1, col2
        FROM table2 poh
        WHERE poh.col1 = 5 and poh.col2 = 4
    )
UPDATE tab1
SET ID = 8, COL1 = 8
FROM table1 tab1
INNER JOIN ctetable CTE ON tab1.ID = CTE.col1;

Snowflake:

UPDATE table1 tab1
SET ID = 8, COL1 = 8
FROM 
(WITH ctetable(col1, col2) as 
    (
        SELECT col1, col2
        FROM table2 poh
        WHERE poh.col1 = 5 and poh.col2 = 4
    ) SELECT * FROM ctetable) AS CTE
WHERE tab1.ID = CTE.col1;

Related EWIs

1. MSCEWI4026