CTEs (WITH clause)

1WITH active_customers AS (
2    SELECT id, name FROM customers WHERE active = true
3)
4SELECT * FROM active_customers ORDER BY name;

 1WITH
 2    dept_stats AS (
 3        SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department
 4    ),
 5    large_depts AS (
 6        SELECT department FROM dept_stats WHERE cnt > 10
 7    )
 8SELECT e.name, e.department
 9FROM employees e
10INNER JOIN large_depts d ON e.department = d.department;

 1WITH RECURSIVE tree(id, name, parent_id, depth) AS (
 2    -- Base case: root nodes
 3    SELECT id, name, parent_id, 0
 4    FROM categories
 5    WHERE parent_id IS NULL
 6
 7    UNION ALL
 8
 9    -- Recursive case: children
10    SELECT c.id, c.name, c.parent_id, t.depth + 1
11    FROM categories c
12    INNER JOIN tree t ON c.parent_id = t.id
13)
14SELECT id, name, depth FROM tree ORDER BY depth, name;