Common Table Expressions (CTEs) and Recursive Queries

A Common Table Expression (CTE) is a named, temporary result set defined with WITH, that you can reference like a table within a single query. Think of it as a "named subquery" that makes complex queries far more readable.

Basic CTE syntax

WITH department_totals AS (
  SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT d.name, dt.employee_count, dt.avg_salary
FROM department_totals dt
JOIN departments d ON d.id = dt.department_id
WHERE dt.employee_count > 4;

Compare this to the equivalent using a subquery in FROM — the CTE version reads top-to-bottom like a story: "first compute department totals, then join them to department names."

Multiple CTEs

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 150000
),
high_earner_departments AS (
  SELECT DISTINCT department_id FROM high_earners
)
SELECT * FROM departments WHERE id IN (SELECT department_id FROM high_earner_departments);

CTEs vs views

A CTE only exists for the duration of the one query it's defined in — nothing is saved to the database. A view is a permanent, reusable, named object. Use a CTE to organize a single complex query; use a view when you'll reuse the same logic across many queries.

Recursive CTEs

A CTE can reference itself — perfect for hierarchical data like an org chart. Let's list every employee along with their "chain of command" depth, starting from department heads:

WITH RECURSIVE org_chart AS (
  -- Anchor: employees with no manager (department heads)
  SELECT id, first_name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive step: find employees managed by someone already in org_chart
  SELECT e.id, e.first_name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, first_name;

A recursive CTE has two parts joined by UNION ALL: the anchor (the starting rows) and the recursive member (which repeatedly joins back to the CTE's own growing result, until no new rows are produced).

Engine note

SQL Server uses plain WITH ... AS (...) for recursive CTEs too (no RECURSIVE keyword needed) — WITH RECURSIVE is the syntax for PostgreSQL, MySQL 8+, and SQLite.

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;