CTEs — WITH Clause

-- Without CTE (nested, hard to read):
SELECT * FROM (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees GROUP BY department
) dept_avg
WHERE avg_sal > 50000;

-- With CTE (clean and readable):
WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
)
SELECT * FROM dept_avg
WHERE avg_sal > 50000;

CTEs improve readability by naming intermediate results. They can be referenced multiple times in the same query (unlike subqueries which must be repeated). Great for multi-step analysis.

WITH company_avg AS (
  SELECT AVG(salary) AS avg_salary
  FROM employees
)
SELECT e.name, e.salary
FROM employees e, company_avg
WHERE e.salary > company_avg.avg_salary;

The CTE calculates the average once and names it. The main query joins it as if it were a table. Much cleaner than repeating the AVG subquery.

WITH
high_earners AS (
  SELECT * FROM employees WHERE salary > 60000
),
dept_counts AS (
  SELECT department_id, COUNT(*) AS cnt
  FROM high_earners
  GROUP BY department_id
)
SELECT d.department_name, dc.cnt
FROM dept_counts dc
JOIN departments d ON dc.department_id = d.id
ORDER BY dc.cnt DESC;

Multiple CTEs are separated by commas. Each CTE can reference previously defined CTEs in the same WITH block. Think of them as named intermediate steps.

-- Generate numbers 1 to 5:
WITH RECURSIVE counter AS (
  SELECT 1 AS n          -- Base case
  UNION ALL
  SELECT n + 1           -- Recursive step
  FROM counter
  WHERE n < 5
)
SELECT n FROM counter;

Recursive CTEs call themselves. Used for hierarchical data (org charts, category trees) and generating sequences. The base case stops infinite recursion.

WITH RECURSIVE org_chart AS (
  -- Start with CEO (no manager)
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Add direct reports of each found employee
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level, name FROM org_chart ORDER BY level;

This traverses the org tree top-down. Level 1 = CEO, Level 2 = their direct reports, etc. Recursive CTEs are the standard way to handle tree-structured data in SQL.