CTEs

CTE stands for common table expression. It involves three parts:

1. Inner query
2. The name of the query and its columns
3. Outer query

Format

The structure of a CTE goes like this:

WITH <name>
AS (
  <inner query>
)
<outer query>

No Nesting

CTEs don't nest unlike derived tables. To handle multiple CTEs, you can separate them using commas:

WITH C1 AS
(
  SELECT...
  FROM T1
  WHERE...
),
C2 AS
(
  SELECT...
  FROM C1
  WHERE...
)
SELECT ...
FROM C2
WHERE ...;

Recursion support

You can do recursion with CTEs. The recursive query can have 2 or more queries usually separated by UNION ALL operator. The requirement is one of the queries in the CTE body needs to return a valid relational result. This is known as the anchor member. This gets executed once. Then one of the queries in the CTE body will need to reference the CTE name. This is the query that will be invoked repeatedly until it returns an empty set. The outer query will then represent the unified results of the anchor member along with the results of the recursive member.

WITH EmpsCTE AS
(-- This is the anchor member
  SELECT empid, mgrid, firstname, lastname, 0 AS distance
  FROM HR.Employees
  WHERE empid = 9

  UNION ALL -- separator

-- This is the recursive member
  SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
  FROM EmpsCTE AS S
    JOIN HR.Employees AS M
      ON S.mgrid = M.empid
)-- Outer query
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE;

It returns the following result:

empid	mgrid	firstname	lastname	distance
9	5	Zoya	Dolgopyatova	0
5	2	Sven	Buck	1
2	1	Don	Funk	2
1	NULL	Sara	Davis	3