A WITH clause is an optional clause that precedes the SELECT list in a query. The WITH clause defines one or more common_table_expressions. Each common table expression (CTE) defines a temporary table, which is similar to a view definition. You can reference these temporary tables in the FROM clause. ()
The is fully supported in Snowflake.
Grammar Syntax
[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]
--Where common_table_expression can be either non-recursive or recursive.
--Following is the non-recursive form:
CTE_table_name [ ( column_name [, ...] ) ] AS ( query )
--Following is the recursive form of common_table_expression:
CTE_table_name (column_name [, ...] ) AS ( recursive_query )
Sample Source Patterns
Recursive form
Input Code:
IN -> Redshift_01.sql
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
WITH RECURSIVE john_org(id, name, manager_id, level) AS
( SELECT id, name, manager_id, 1 AS level
FROM employee
WHERE name = 'John'
UNION ALL
SELECT e.id, e.name, e.manager_id, level + 1 AS next_level
FROM employee e, john_org j
WHERE e.manager_id = j.id and level < 4
)
SELECT DISTINCT id, name, manager_id FROM john_org ORDER BY manager_id;
ID
NAME
MANAGER_ID
101
John
100
110
Liu
101
102
Jorge
101
103
Kwaku
101
201
Sofía
102
106
Mateo
102
105
Richard
103
104
Paulo
103
110
Nikki
103
205
Zhang
104
120
Saanvi
104
200
Shirley
104
Output Code:
OUT -> Redshift_01.sql
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "11/05/2024", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
WITH RECURSIVE john_org(id, name, manager_id, level) AS
( SELECT id, name, manager_id, 1 AS level
FROM
employee
WHERE
RTRIM( name) = RTRIM( 'John')
UNION ALL
SELECT e.id, e.name, e.manager_id, level + 1 AS next_level
FROM
employee e,
john_org j
WHERE e.manager_id = j.id and level < 4
)
SELECT DISTINCT id, name, manager_id FROM
john_org
ORDER BY manager_id;
ID
NAME
MANAGER_ID
101
John
100
102
Jorge
101
103
Kwaku
101
110
Liu
101
106
Mateo
102
201
Sofía
102
110
Nikki
103
104
Paulo
103
105
Richard
103
120
Saanvi
104
200
Shirley
104
205
Zhang
104
Non recursive form
Input Code:
IN -> Redshift_02.sql
WITH ManagerHierarchy AS (
SELECT id AS employee_id, name AS employee_name, manager_id
FROM employee
)
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM ManagerHierarchy e
LEFT JOIN ManagerHierarchy m ON e.manager_id = m.employee_id;
EMPLOYEE
MANAGER
Carlos
null
John
Carlos
Jorge
John
Kwaku
John
Liu
John
Mateo
Jorge
Sofía
Jorge
Nikki
Kwaku
Paulo
Kwaku
Richard
Kwaku
Saanvi
Paulo
Shirley
Paulo
Zhang
Paulo
Output Code:
OUT -> Redshift_02.sql
WITH ManagerHierarchy AS (
SELECT id AS employee_id, name AS employee_name, manager_id
FROM
employee
)
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM
ManagerHierarchy e
LEFT JOIN
ManagerHierarchy m ON e.manager_id = m.employee_id;
