WITH (Common Table Expressions)

Non-recursive (standard) CTE:

WITH cte [, ...]

cte:
    expression_name[(column_name, ...)]
    AS
    select_statement

Recursive CTE:

WITH RECURSIVE cte [, ...]

cte:
    expression_name [(column_name, ...)] AS (
    <base_case> 
    UNION ALL
    <recursive_case>
    ) select_statement;

A non-recursive standard CTE does not reference itself within the statement it is executed in. Non-recursive CTEs are inline view definitions that appear at the start of a query. Also, a non-recursive CTE can be used to simplify a query by isolating subselects out of the main SQL statement.

A recursive CTE references itself. They are useful when working with hierarchical data because the CTE continues to execute until the query returns the desired portion of the hierarchy.

A common example of hierarchical data is an organization chart where employees are listed under their direct managers.

This shows, in a graphical form, that a recursive CTE can retrieve from the database by recursively expanding the result set starting at the top with Maggie. The result set will still be in the form of a table.

A recursive CTE contains two SELECT statements joined by a UNION ALL clause. The first SELECT statement is the base case which is executed once. The second SELECT statement is the recursive case where the CTE references itself, expanding repeatedly until the termination condition is met. The termination condition does not evaluate to TRUE or FALSE. The termination condition is not specific syntax; rather, the RCTE terminates when the recursive_case does not return any new rows.

Non-recursive CTEs:

WITH myCTE AS
(select col from myTable)
select col from myCTE;

WITH myCTE (colAlias) AS
(select col from myTable)
select colAlias from myCTE;

WITH orderCTE AS
(select o_orderkey from orders),
lineitemCTE AS
(select l_orderkey from lineitem)
select count(*) from orderCTE join lineitemCTE on o_orderkey = l_orderkey;

WITH foo AS
(WITH bar AS
(SELECT * FROM t) SELECT * FROM bar)
SELECT * FROM foo;

WITH EmpSal(averageSal) AS
(SELECT AVG(Salary) FROM Employee)
SELECT EmpID, Name, Salary FROM Employee, EmpSal 
WHERE Employee.Salary > EmpSal.averageSal;

WITH ObjectCTE (Name, Id, Date) AS
(SELECT objname, objectid, invoicedate FROM Inventory)
INSERT INTO Itemlist(Name, objectid, createdDate)
SELECT Name, Id, Date FROM ObjectCTE; 

SELECT * FROM Itemlist;

Recursive CTEs:

Using the organization chart described above, this example illustrates how recursive CTEs simplify the retrieval of hierarchical data.

CREATE TABLE cte_emp(id INT, mgr_id INT, name VARCHAR(30));
INSERT INTO cte_emp VALUES(1, NULL, "Maggie"), (2, 1, "John"), (3, 1, "Felix"), 
                      (4, 2, "Joan"), (5, 2, "Richard"), (6, 3, "Andy"), (7, 3, "Jill");

WITH RECURSIVE org_chart (emp_id, mgr_id, name, level) AS (         
    SELECT cte_emp.id, cte_emp.mgr_id, cte_emp.name, 0 AS level                  
    FROM cte_emp          
    WHERE cte_emp.mgr_id is NULL               
    UNION ALL     
    SELECT cte_emp.id, cte_emp.mgr_id, cte_emp.name, level+1           
    FROM cte_emp           
    INNER JOIN org_chart                
    ON cte_emp.mgr_id = org_chart.emp_id    
) SELECT * FROM org_chart;
****
+------+--------+---------+-------+
| id   | mgr_id | name    | level |
+------+--------+---------+-------+
|    1 |   NULL | Maggie  |     0 |
|    3 |      1 | Felix   |     1 |
|    2 |      1 | John    |     1 |
|    5 |      2 | Richard |     2 |
|    4 |      2 | Joan    |     2 |
|    7 |      3 | Jill    |     2 |
|    6 |      3 | Andy    |     2 |
+------+--------+---------+-------+
7 rows in set (0.58 sec)

Another example is a graph of connected nodes. A recursive CTE can retrieve the nodes and their relationships to one other. The id column is the node id and pr is the parent node to the node in the id column.

CREATE TABLE cte_g (id INT, pr INT);
INSERT INTO cte_g VALUES (0, null), (1, 0), (2, 0),(3, 1), (4, 1), (5, 2), (6, 2);

WITH RECURSIVE depths AS (  
    SELECT 0 depth, * FROM cte_g WHERE id = 0      
    UNION ALL     
    SELECT depth+1, cte_g.id, cte_g.pr FROM depths 
    JOIN cte_g ON cte_g.pr = depths.id
) SELECT depth, id FROM depths;
****
+-------+------+
| depth | id   |
+-------+------+
|     0 |    0 |
|     1 |    1 |
|     1 |    2 |
|     2 |    5 |
|     2 |    3 |
|     2 |    4 |
|     2 |    6 |
+-------+------+
7 rows in set (0.55 sec)