SQL Tutorial · Joins and Subqueries · Lesson 26 of 36

SQL WITH (CTEs)

Name subqueries with WITH, and walk hierarchies with recursive CTEs.

Common Table Expressions

WITH name AS (...) defines a named subquery you can use in the main query — like a variable for a result set. CTEs make multi-step queries readable top to bottom:

Example 1Try it yourself

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
),
big_orders AS (
  SELECT * FROM order_totals WHERE total > 100
)
SELECT o.order_id, c.name, ROUND(b.total, 2) AS total
FROM big_orders b
JOIN orders o ON o.order_id = b.order_id
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY b.total DESC;

Output

order_id | name | total
9 | Ada Lovelace | 658
4 | Grace Hopper | 408.9
7 | Grace Hopper | 114.99
5 | Linus Torvalds | 114.98
2 | Alan Turing | 113.99
12 | Linus Torvalds | 102

Recursive CTEs

A recursive CTE refers to itself, which lets it walk trees and generate sequences. Here it finds everyone under the CTO:

Example 2Try it yourself

WITH RECURSIVE team(employee_id, name, level) AS (
  SELECT employee_id, name, 0 FROM employees WHERE title = 'CTO'
  UNION ALL
  SELECT e.employee_id, e.name, t.level + 1
  FROM employees e JOIN team t ON e.manager_id = t.employee_id
)
SELECT substr('        ', 1, level * 2) || name AS org_chart FROM team ORDER BY level, name;

Output

org_chart
Omar Haddad
  Mei Chen
    Jonas Berg
    Lucas Martin
    Priya Nair

Test yourself

Which keyword starts a common table expression?

CTE
WITH
DEFINE
LET

Show answer

Answer: WITH