WITH
Defines a named subquery (a common table expression, CTE) that the main query can use like a table.
WITH [RECURSIVE] name_cte [(column[, ...])] AS (subquery) main_queryParameters
- RECURSIVEkeywordoptional
- Allows the table expression to refer to itself
- name_ctename
- Name of the table expression
- columnnameoptional
- Column names of the table expression, taken from subquery by default
- subquerysubquery
- Subquery whose result is available by the name name_cte
- main_querysubquery
- Main query that refers to name_cte like a table
Return value
A temporary named result set available only within this query
Examples
Statuses with more than one member
WITH status_counts AS (
SELECT status,
COUNT(*) AS members_count
FROM FamilyMembers
GROUP BY status
)
SELECT status,
members_count
FROM status_counts
WHERE members_count > 1
ORDER BY status;Numbers 1 to 5 with RECURSIVE
WITH RECURSIVE numbers (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 5
)
SELECT n
FROM numbers;Subordinates of managers by level
WITH RECURSIVE subordinates AS (
SELECT id,
name,
manager_id,
1 AS LEVEL
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id,
e.name,
e.manager_id,
s.level + 1
FROM employees AS e
JOIN subordinates AS s ON e.manager_id = s.id
)
SELECT id,
name,
LEVEL
FROM subordinates
ORDER BY LEVEL,
id;Details
One WITH can define several expressions separated by commas, and each one can refer to the previous ones.
A recursive expression (WITH RECURSIVE) has two parts joined with UNION ALL or UNION. The initial query runs once and gives the first rows. The recursive part refers to the expression itself and runs again and again: at each step it sees only the rows added at the previous step. The work stops when a step adds no rows.
There is no limit on the number of steps, so the recursive part needs a stop condition, for example WHERE n < 5. UNION drops duplicate rows, which can also stop the recursion, while UNION ALL keeps them all.