Common Table Expressions (CTEs)

What You'll Learn

A CTE is a named temporary result set defined with the WITH clause before the main query. CTEs improve readability by breaking complex queries into named steps. Multiple CTEs can be chained. Recursive CTEs can traverse hierarchies like org charts or category trees.

Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

-- Multiple CTEs
WITH cte1 AS (...), cte2 AS (...)
SELECT ...;

Example

-- Basic CTE
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*) AS count
FROM high_earners
GROUP BY department;

-- Chained CTEs
WITH
monthly_rev AS (
  SELECT DATE_FORMAT(order_date,'%Y-%m') AS mo,
         SUM(total) AS rev
  FROM orders GROUP BY mo
),
avg_rev AS (
  SELECT AVG(rev) AS avg FROM monthly_rev
)
SELECT mo, rev FROM monthly_rev, avg_rev
WHERE rev > avg;

-- Recursive CTE: number series 1-10
WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

Common Mistakes

Interview Tips

Practice