CTE (Common Table Expression) — SQL

A CTE is a query given a name with WITH: WITH name AS (SELECT ...). Right after it comes the main query (the one that gives the answer), which uses name like…

Read this recipe step: "pour into the pan the spice paste ground from the onions you sliced". To make sense of it, you have to read it backwards: slice the onions, grind them, then pour. A recipe that is easy to read names the step in the middle: make the spice paste first, then "pour the spice paste into the pan".

A query with nested subqueries (a subquery inside a subquery) has the same problem: it reads from the inside out. A CTE (Common Table Expression) does what the second recipe does. With WITH, you give a query a name at the start, like the spice paste you make first. Then the main query (the last query, the one that gives the answer) uses that name like any other table, like the step "pour the spice paste into the pan". So the query reads from top to bottom.

From subquery to CTE

In the Subquery lesson you found the products priced above the average with WHERE price > (SELECT AVG(price) FROM products). Now the question is the same, but the data is different: the employees table holds 5 employees, with the columns id, name and salary. Their salaries: Budi 5,000, Siti 7,000, Andi 9,000, Dewi 12,000, and Rizky 4,000.

The subquery version, the one you already know:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

The CTE version:

WITH avg_salary AS (
  SELECT AVG(salary) AS amount FROM employees
)
SELECT name, salary
FROM employees
WHERE salary > (SELECT amount FROM avg_salary)
ORDER BY salary DESC;

Both return the same 2 rows: Dewi 12,000, then Andi 9,000. A CTE changes no result. It only moves the subquery to the top and gives it a name.

Several CTEs in a row

Now there are two new questions: how many people earn above the average, and what is their average salary? With subqueries, the answer becomes parentheses inside parentheses. There is a subquery in FROM, used like a table, so it must be named with AS. Inside that subquery is another subquery:

SELECT COUNT(*) AS headcount, AVG(salary) AS average_salary
FROM (
  SELECT name, salary FROM employees
  WHERE salary > (SELECT AVG(salary) FROM employees)
) AS high_earners;

With CTEs, the steps line up from top to bottom:

WITH avg_salary AS (
  SELECT AVG(salary) AS amount FROM employees
),
high_earners AS (
  SELECT name, salary FROM employees
  WHERE salary > (SELECT amount FROM avg_salary)
)
SELECT COUNT(*) AS headcount, AVG(salary) AS average_salary
FROM high_earners;

Both give the same result: headcount 2 and average_salary 10,500 (the average salary of Dewi and Andi).

WITH RECURSIVE: walking a tree

The categories of an online shop are often a tree: one category has children (subcategories under it), and a child can have children too. Each category stores a parent_id, the id of the category above it (NULL for a category at the top). This is the tree, with each category's id in parentheses:

Electronics (1)
  Computers (2)
    Laptop (4)
    Accessories (5)
      Mouse (6)
  Phones (3)
Fashion (7)
  T-shirts (8)

You want every subcategory under Electronics, however deep. One plain JOIN only goes down one level, for example from Electronics to Computers. WITH RECURSIVE repeats the JOIN, round after round, until there are no children left. The query has two parts joined by UNION ALL (which stacks the results of two queries into one):

WITH RECURSIVE category_tree AS (
  -- anchor: the starting point, runs once
  SELECT id, name, parent_id, 0 AS level
  FROM categories
  WHERE id = 1

  UNION ALL

  -- recursive: children of the rows found so far
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

This is SQLite's result. The Round column is not in the real result: it is added here so you can see which round found each row.

Round id name parent_id level
anchor 1 Electronics NULL 0
1 2 Computers 1 1
1 3 Phones 1 1
2 5 Accessories 2 2
2 4 Laptop 2 2
3 6 Mouse 5 3

The word RECURSIVE must be written. SQLite still runs the query when you forget it, but PostgreSQL and MySQL reject it.

CTE or subquery?

Subquery CTE
Shape in parentheses, in the middle of the query named at the top, with WITH
How it reads from the inside out from top to bottom
Used twice typed out again written once
Recursion not possible possible, with WITH RECURSIVE
Speed the same in most databases the same in most databases

A CTE fits better when the same result is used two or more times, when the query starts getting hard to read, or when you need recursion. A CTE is not a trick to make a query faster. For one number used once, a short subquery is enough.

📌 SQLite, PostgreSQL and MySQL differ

The exercise and the Lab here use SQLite. CTEs and WITH RECURSIVE are in SQLite (since version 3.8.3), PostgreSQL, and MySQL 8.0 and up. MySQL 5.7 has no CTEs at all.

⚠️ Stray semicolons and recursion that never stops

TL;DR: A CTE is a query given a name with WITH: WITH name AS (SELECT ...). Right after it comes the main query (the one that gives the answer), which uses name like a table, with no semicolon in between. The result is the same as with a subquery, but the query reads from top to bottom, and a CTE's result can be used many times. Several CTEs are written under one WITH, separated by commas. WITH RECURSIVE repeats a query to walk data shaped like a tree, such as categories and their subcategories. It holds an anchor (the starting point) that runs once, UNION ALL that stacks the results, and a recursive part that uses the rows from the round before. The repeating stops when a round finds no new rows, so make sure it has a stop condition. A CTE doesn't make a query faster: use one when a result is used twice, the query is hard to read, or you need to repeat a query with WITH RECURSIVE.

⚡ Name the step in the middle

Write:

WITH avg_salary AS (
  SELECT AVG(salary) AS amount FROM employees
)
SELECT name, salary
FROM employees
WHERE salary > (SELECT amount FROM avg_salary)
ORDER BY salary DESC;

Meaning: This query looks for the employees in the employees table who earn above the average. WITH gives the name avg_salary to the step in the middle: the query that calculates the average salary. A query given a name with WITH is called a CTE. The main query below it uses avg_salary like a table. The result is the same as with the subquery (SELECT AVG(salary) FROM employees): Dewi, then Andi.

⚡ No semicolon in the middle

Meaning: The CTE and its main query are one command, so there's only one semicolon, at the very end. To the main query, a CTE is a table, not a variable (a name for one value): fetch its value with (SELECT amount FROM avg_salary), where amount is its column's name.

⚡ Several CTEs in a row

Meaning: One WITH is enough, and the next CTEs are separated by commas. A CTE further down may use the ones above it, so the query reads from top to bottom.

⚡ CTE or subquery?

Guess first: a CTE and a subquery give the same result. When is a CTE the better choice?

When the same result is used twice or more, when the query is hard to read, or when you need recursion (a query that repeats itself, written WITH RECURSIVE). A CTE doesn't make a query faster.

Meaning: WITH RECURSIVE can walk a category tree (categories, subcategories and so on) however deep it goes, as long as it has a stop condition.

What you will learn