AI Hints
Homeโ€บSQL Tutorialโ€บCTEs, EXISTS, and Set Operations
SQL ยท Lesson 58 of 126

Common Table Expressions

WITH makes queries readable.

โฑ 2 min readโ–ถ Video at 1:22:59๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Subqueries are powerful, but they get messy. A subquery inside a subquery, inside another one, is very hard to read. There's a tidier way. You give each step a name, and write the steps in order, from top to bottom. It's called a common table expression, or CTE.

Common Table Expressions
From the video: Common Table Expressions
  • Nested subqueries get messy
  • Name each step
  • Read top to bottom

Your first CTE

Start with the word with, then a name, then as, and the query in brackets. Here, monthly is a little table of orders per month. Then, below it, the main query uses monthly, just like a normal table. Run it. Helen gets about sixty seven orders in an average month. Her best month had one hundred and twenty.

query.sqlsql
WITH monthly AS (
    SELECT strftime('%Y-%m', order_date) AS month,
           COUNT(*) AS orders
    FROM orders
    GROUP BY month
)
SELECT ROUND(AVG(orders), 1) AS avg_per_month,
       MAX(orders)           AS best_month
FROM monthly;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ avg_per_month โ”‚ best_month โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 66.6          โ”‚ 120        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Use it twice

ACTE can be used more than once. Here, monthly appears in the from, and again in the subquery, to find months above average. Run it. August twenty twenty five was the busiest, then December, then May. All the best months are in twenty twenty five.

query.sqlsql
WITH monthly AS (
    SELECT strftime('%Y-%m', order_date) AS month,
           COUNT(*) AS orders
    FROM orders
    GROUP BY month
)
SELECT month, orders
FROM monthly
WHERE orders > (SELECT AVG(orders) FROM monthly)
ORDER BY orders DESC
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  month  โ”‚ orders โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2025-08 โ”‚ 120    โ”‚
โ”‚ 2025-12 โ”‚ 104    โ”‚
โ”‚ 2025-05 โ”‚ 103    โ”‚
โ”‚ 2025-09 โ”‚ 93     โ”‚
โ”‚ 2025-10 โ”‚ 93     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Subquery versus CTE

Both do the same job. But a CTE reads from top to bottom, has a clear name, and can be reused. Most professionals prefer CTEs for anything longer than a couple of lines.

Subquery

  • Nested inside
  • No name
  • Read inside out

CTE (WITH)

  • Written first
  • Has a name
  • Read top to bottom

Summary

With, a name, and a query in brackets. That's a CTE. It turns a messy query into simple, named steps. Next, chaining several of them together.

Key takeaways
  • WITH name AS (...)
  • Then use it like a table
  • Reusable and readable
Next up: Chaining CTEs โ€” step by step analysis