Common Table Expressions (CTEs)

A Common Table Expression, a CTE, gives a subquery a name using WITH, so the rest of the query can reference it like a regular table. It doesn't do anything a subquery in FROM couldn't already do, the real benefit is readability: breaking a complex query into named, sequential steps instead of nesting everything inside itself.

Learning Objectives

• Write a basic CTE with WITH, and reference it in the main query.
• Chain multiple CTEs together in one query.
• Write a simple recursive CTE to walk a hierarchy.

A Basic CTE

high_earners is defined once with WITH, then used in the main query exactly like a real table. This is identical in effect to using the same query as a subquery in FROM, written this way it's just easier to read top to bottom.

WITH high_earners AS (
  SELECT first_name, department, salary
  FROM employees
  WHERE salary > 90000
)
SELECT department, COUNT(*) AS headcount
FROM high_earners
GROUP BY department;

Chaining Multiple CTEs

Separate CTEs with a comma, and each one can reference any CTE defined before it. This query first computes department averages, then uses that result to find employees earning above their own department's average, each step named and readable on its own.

WITH department_averages AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
),
above_average AS (
  SELECT e.first_name, e.department, e.salary
  FROM employees e
  JOIN department_averages d ON e.department = d.department
  WHERE e.salary > d.avg_salary
)
SELECT * FROM above_average;

Recursive CTEs

A recursive CTE references itself, built from a starting point (the "anchor," here, employees with no manager) and a repeating step (UNION ALL, joining back to the CTE itself to find the next level down), until no new rows are found. This is the standard way to walk a hierarchy, an org chart, a category tree, a folder structure, in SQL.

WITH RECURSIVE org_chart AS (
  SELECT id, first_name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.first_name, e.manager_id, org_chart.level + 1
  FROM employees e
  JOIN org_chart ON e.manager_id = org_chart.id
)
SELECT * FROM org_chart;

WITH RECURSIVE org_chart AS (
  SELECT id, first_name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.first_name, e.manager_id, org_chart.level + 1
  FROM employees e
  JOIN org_chart ON e.manager_id = org_chart.id
)
SELECT * FROM org_chart;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Use a CTE to name each logical step of a complex query, rather than nesting subqueries three levels deep.
• Always write RECURSIVE explicitly for a self-referencing CTE, it's required syntax on every database in this course, not an optional keyword.
• Default to UNION ALL inside a recursive CTE's repeating step, not UNION, for both performance and correctness.
• Reach for a view instead of a CTE when the same named query needs to be reused across multiple separate statements.

Interview Questions

Summary

This lesson covered naming a subquery with WITH for readability, chaining multiple CTEs where each can reference the ones before it, and recursive CTEs for walking a hierarchy, built from an anchor query and a repeating step joined back to itself.

What's Next?

The next lesson covers set operations, UNION, INTERSECT, and EXCEPT, combining the results of two separate queries vertically.