Common Table Expressions

Part 1: Concept Notes: Common Table Expressions

Introduction

What is it? A query like SELECT ... FROM ... produces a result set, which is a table-like output. A CTE (Common Table Expression) is a way to give that result set a name and then use the name inside the same final query.

The high-level idea:

1. Run a query that produces a result set.
2. Give that result set a name, like a temporary "table".
3. Write another query that selects from that named result set.

Formally:

• CTE stands for Common Table Expression.
• It is a temporary named result set.
• It is written at the top of a query using the keyword WITH.
• It exists only while that one final query runs.

A small reminder: the keyword is WITH, not "width".

Why do we need it? Without CTEs, complex logic ends up as subqueries inside subqueries inside subqueries, and nobody can read it. CTEs let you write the logic as clean, ordered steps:

Real-world analogy Think of cooking from a recipe. First you prepare the sauce (step 1) and set it aside in a labeled bowl. Then you cook the pasta (step 2) in another labeled bowl. At the end, you combine the bowls. Each bowl has a name, and the final dish uses the bowls by name. A CTE is one of those labeled bowls.

Business use case A food-delivery company wants restaurants whose average rating is above the overall average. Step 1: calculate each restaurant's average. Step 2: calculate the overall average. Step 3: compare them. With CTEs, each step has a name and the final query simply compares the two named results.

Dataset Used

Three tables are used in this chapter.

ratings (customer ratings for restaurants)

CREATE TABLE ratings (
  rating_id INT PRIMARY KEY,
  restaurant VARCHAR(100) NOT NULL,
  stars TINYINT NULL,
  CHECK (stars IS NULL OR (stars BETWEEN 1 AND 5))
) ENGINE=InnoDB;

INSERT INTO ratings (rating_id, restaurant, stars) VALUES
(1, 'Cafe Aroma', 4),
(2, 'Cafe Aroma', 5),
(3, 'Pasta Hub', 3),
(4, 'Pasta Hub', 4),
(5, 'Spice Box', 5),
(6, 'Burger Barn', 4),
(7, 'Burger Barn', NULL),
(8, 'Sushi Zen', 4),
(9, 'Sushi Zen', 4),
(10, 'Taco Town', 2),
(11, 'Taco Town', 3),
(12, 'Noodle Nest', 4);

sales_date (sales made by salespeople)

CREATE TABLE sales_date (
  sale_id INT PRIMARY KEY,
  salesperson VARCHAR(50) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  INDEX idx_sales_date_salesperson (salesperson),
  INDEX idx_sales_date_amount (amount)
) ENGINE=InnoDB;

INSERT INTO sales_date (sale_id, salesperson, amount) VALUES
(101, 'Aditi', 500),
(102, 'Rahul', 300),
(103, 'Aditi', 700),
(104, 'Karan', 250),
(105, 'Neha', 900),
(106, 'Rahul', 150),
(107, 'Karan', 100),
(108, 'Ishan', 600),
(109, 'Ishan', 100),
(110, 'Neha', 200),
(111, 'Rahul', 50),
(112, 'Karan', 20);

categories (a product category tree, used for recursive CTEs)

CREATE TABLE categories (
    cat_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT NULL,
    INDEX idx_categories_parent_id (parent_id)
) ENGINE = InnoDB;

INSERT INTO categories (cat_id, name, parent_id) VALUES
    (1,  'Electronics', NULL),
    (2,  'Mobiles', 1),
    (3,  'Accessories', 2),
    (4,  'Cables', 3),
    (5,  'Chargers', 3),
    (6,  'Home', NULL),
    (7,  'Kitchen', 6),
    (8,  'Appliances', 6),
    (9,  'Smartphones', 2),
    (10, 'Android', 9),
    (11, 'iOS', 9),
    (12, 'OrphanCat', 999);

The tree as a picture:

 Electronics (1)
 +-- Mobiles (2)
      +-- Accessories (3)
      |     +-- Cables (4)
      |     +-- Chargers (5)
      +-- Smartphones (9)
            +-- Android (10)
            +-- iOS (11)

 Home (6)
 +-- Kitchen (7)
 +-- Appliances (8)

 OrphanCat (12)   its parent_id 999 does not exist, so it hangs on nothing

Things to notice in the data, because they matter later:

• Burger Barn has one rating of 4 and one rating of NULL.
• Two separate trees exist (Electronics and Home), plus one orphan category.

Syntax

Basic CTE

WITH cte_name AS (
  -- any SELECT that returns a table-like result
  SELECT ...
)
SELECT ...
FROM cte_name;

Several CTEs in a row (separated by commas, only one WITH)

WITH first_cte AS (
  SELECT ...
),
second_cte AS (
  SELECT ... FROM first_cte      -- a later CTE can use an earlier one
)
SELECT ...
FROM second_cte;

With explicit column names (optional, for clarity)

WITH cte_name (col_a, col_b) AS (
  SELECT x, y FROM some_table
)
SELECT col_a, col_b FROM cte_name;

Recursive CTE

WITH RECURSIVE name AS (
  -- Anchor query (the starting point)
  SELECT ...
  UNION ALL
  -- Recursive query (refers to the CTE's own name)
  SELECT ... FROM name WHERE stop_condition
)
SELECT * FROM name;

Good practices

• Use descriptive CTE names (avoid names like demo in production code).
• Avoid a CTE name that matches an existing table name. Inside the query, the CTE name would hide the real table.
• Optionally list the column names explicitly.

Mental Model

A CTE is a labeled bowl that holds the result of a small query. The final query picks up the bowls by name.

 Nested subqueries (hard to read)          CTEs (read top to bottom)

 SELECT ...                                WITH step_1 AS ( ... ),
 FROM (                                         step_2 AS ( ... step_1 ... )
    SELECT ...                             SELECT ...
    FROM (                                 FROM step_2;
       SELECT ...
       FROM ...
    ) a
 ) b;

 You read from the inside out.             You read from the top down.

The "pipeline" view of several CTEs:

 +----------+     +----------+     +-------------+
 | step_1   | --> | step_2   | --> | FINAL QUERY |
 | (named)  |     | (named)  |     | (result)    |
 +----------+     +----------+     +-------------+

How SQL Executes It

1. SQL reads the WITH block and works out each CTE (logically, in the order written).
2. Each CTE becomes a temporary named result set with rows and columns.
3. The final query reads from those named result sets like ordinary tables.
4. When the final query finishes, the CTEs disappear. They are not saved anywhere.

Two points to remember:

• A CTE only lives for one statement. You cannot use it in the next query.
• The database decides how to run it. It may calculate the CTE once and store it briefly, or fold it into the main query. The result is the same either way.

1. Your First CTE: A One-Row Result

Problem Create the smallest possible CTE, and read from it.

SQL Query

WITH e_row AS (
  SELECT 1 AS n
)
SELECT n
FROM e_row;

Explanation The CTE e_row holds a result with one column n and one row containing 1. The final query reads n from it, just like from a table.

Output

Why it works A CTE output is always a table-like structure, which means rows and columns. Even when it holds "one value", it is really 1 row by 1 column.

2. Basic CTE

A basic CTE computes a result once, gives it a name, and reuses it in the final query.

Problem Calculate each restaurant's average rating, and keep restaurants with an average above 4.0.

SQL Query

WITH avg_ratings AS (
  SELECT restaurant, AVG(stars) AS avg_stars
  FROM ratings
  GROUP BY restaurant
)
SELECT restaurant, avg_stars
FROM avg_ratings
WHERE avg_stars > 4.0
ORDER BY restaurant ASC;

Explanation (step by step)

1. avg_ratings computes one row per restaurant with its average stars.
2. The final query selects from avg_ratings as if it were a table.
3. It keeps only restaurants whose average is greater than 4.0.

The CTE's content (what avg_ratings holds)

Output

Why it works AVG() ignores NULL. That is why Burger Barn's average is 4.0: only one valid rating (the 4) is counted, and the NULL is skipped. Since 4.0 is not greater than 4.0, Burger Barn is not in the output. (MySQL may display the averages with 4 decimals, such as 4.5000. It is the same number.)

3. Avoid ORDER BY Inside a CTE

Ordering inside a CTE usually adds unnecessary work, because the final query may reorder or filter the rows again. Put ORDER BY in the final query, unless you truly need ordering together with LIMIT.

Problem Select restaurants whose average rating is greater than 4.0, and also show their minimum rating.

SQL Query

WITH avg_ratings AS (
  SELECT restaurant,
         AVG(stars) AS avg_stars,
         MIN(stars) AS min_rating
  FROM ratings
  GROUP BY restaurant
)
SELECT restaurant,
       avg_stars,
       min_rating
FROM avg_ratings
WHERE avg_stars > 4.0
ORDER BY restaurant ASC;     -- ORDER BY is in the final query, not in the CTE

Explanation (step by step)

1. The CTE calculates two things for each restaurant: the average rating and the lowest rating. It groups the rows by restaurant.
2. The outer query selects restaurant, average rating, and minimum rating.
3. It keeps only rows where avg_stars > 4.0.
4. It orders the result alphabetically.

Output

Why it works One CTE can hold several calculated columns, and the final query simply picks from them. Sorting once at the end is cheaper than sorting inside the CTE and then again outside.

4. Subquery vs CTE

Problem Find restaurants whose average rating is above the overall average rating of all ratings.

Subquery version

SELECT
  restaurant,
  AVG(stars) AS avg_stars
FROM ratings
GROUP BY restaurant
HAVING AVG(stars) > (SELECT AVG(stars) FROM ratings)
ORDER BY avg_stars DESC, restaurant ASC;

CTE version

WITH baseline AS (
  SELECT AVG(stars) AS overall_avg_stars
  FROM ratings
),
avg_by_restaurant AS (
  SELECT restaurant, AVG(stars) AS avg_stars
  FROM ratings
  GROUP BY restaurant
)
SELECT
  a.restaurant,
  a.avg_stars
FROM avg_by_restaurant a
CROSS JOIN baseline b
WHERE a.avg_stars > b.overall_avg_stars
ORDER BY a.avg_stars DESC, a.restaurant ASC;

Why CROSS JOIN? baseline returns exactly one row (the one overall average). We want that one value to be available for comparison with every restaurant row. A CROSS JOIN with a one-row table attaches that value to every restaurant row.

What the two CTEs hold

The overall average is 42 / 11 = 3.818181... (11 valid ratings add up to 42, and AVG ignores the NULL). So any restaurant whose average is higher than 3.818... is selected.

Output (the same for both queries)

Why the order looks like this The sort is avg_stars DESC first, and restaurant ASC second. The three restaurants tied at 4.0 are therefore listed alphabetically: Burger Barn, Noodle Nest, Sushi Zen.

Subquery vs CTE side by side

A CTE is mainly a readability tool. It is not automatically faster than a subquery.

5. Multi-step CTE Pipeline

A multi-step pipeline means: create one CTE, then let the next CTE use the previous one, and so on. Each CTE does one logical job.

Problem Find the top-performing salesperson by total sales.

The plan (how you would think about it with GROUP BY)

1. Group sales by salesperson.
2. Compute SUM(amount) for each salesperson.
3. Pick the maximum total.
4. Return the salesperson(s) whose total equals that maximum.

SQL Query

WITH totals AS (
  SELECT salesperson, SUM(amount) AS total_sales
  FROM sales_date
  GROUP BY salesperson
),
max_sales AS (
  SELECT MAX(total_sales) AS max_total_sales
  FROM totals
)
SELECT t.salesperson, t.total_sales
FROM totals t
JOIN max_sales m
  ON t.total_sales = m.max_total_sales
ORDER BY t.salesperson ASC;

 sales_date --> [ totals ] --> [ max_sales ] --+
                    |                          |
                    +--------- JOIN -----------+--> top salesperson(s)

Explanation (step by step)

1. totals creates one row per salesperson with their total sales.
2. max_sales takes the highest total from totals.
3. The final query joins both and keeps only the salesperson(s) whose total equals the maximum.

What the CTEs hold

Output

Why it works Notice that totals is used twice: once by max_sales, and once in the final query. With plain subqueries, you would have to write the GROUP BY code twice. With a CTE, you write it once and name it. Also, because the final step matches on "equal to the maximum", a tie for first place would return all tied salespeople. (ORDER BY total_sales DESC LIMIT 1 would return only one.)

6. Recursive CTE (Hierarchical Data)

A recursive CTE is a CTE that refers to itself. It is used for hierarchical data (trees) and for generating sequences. It has two parts:

A proper stopping condition is very important. Without it, the recursion would never end.

Syntax

WITH RECURSIVE name AS (
  -- Anchor Query
  UNION ALL
  -- Recursive Query
)
SELECT * FROM name;

 Round 0:  Anchor query runs            -> first set of rows
 Round 1:  Recursive query uses round 0 -> new rows
 Round 2:  Recursive query uses round 1 -> new rows
 ...
 Round N:  Recursive query finds nothing -> STOP
 Final result = all rounds stacked together

Example 6.1: Count from 1 to 5

SQL Query

WITH RECURSIVE demo AS (
  SELECT 1 AS n                 -- Anchor query (starting point)
  UNION ALL
  SELECT n + 1
  FROM demo
  WHERE n < 5                   -- Stop condition
)
SELECT * FROM demo;

Explanation

1. The anchor query runs first and gives n = 1.
2. The recursive part says: take the previous number, add 1, and repeat while n < 5.
3. So it goes 1 => 2, 2 => 3, 3 => 4, 4 => 5.
4. When n = 5, the condition n < 5 is false, so nothing new is produced and the recursion stops.

Round by round

Output

Why it works Each round only works with the rows created in the previous round, not the whole table. The WHERE condition is what finally produces "no new rows", which ends the loop.

Example 6.2: The category tree for Electronics

Problem List Electronics and everything under it, with the level of each category (Electronics is level 1).

SQL Query

WITH RECURSIVE category_tree AS (
  -- Anchor: start at Electronics
  SELECT cat_id, `name`, parent_id, 1 AS level
  FROM categories
  WHERE cat_id = 1

  UNION ALL

  -- Recursive: children of the previous level
  SELECT c.cat_id, c.`name`, c.parent_id, ct.level + 1 AS level
  FROM categories c
  JOIN category_tree ct
    ON c.parent_id = ct.cat_id
)
SELECT cat_id, name, parent_id, level
FROM category_tree
ORDER BY level ASC, cat_id ASC;

Step-by-step dry run (intuitive)

1. Start at Electronics (level 1).
2. Find children whose parent_id = 1: Mobiles (level 2).
3. Find children of Mobiles (parent_id = 2): Accessories and Smartphones (level 3).
4. Find children of those (level 4): Cables, Chargers, Android, and iOS.
5. Stop when no more children are found.

 Round 0 (anchor):   Electronics                      level 1
                          |
 Round 1:                 Mobiles                     level 2
                          |
 Round 2:        Accessories, Smartphones             level 3
                     |            |
 Round 3:   Cables, Chargers   Android, iOS           level 4
                          |
 Round 4:  no children found  ->  STOP

Output

Why it works In the recursive part, ct.level + 1 makes the level grow by one each round, and the join c.parent_id = ct.cat_id finds the children of the rows from the previous round. OrphanCat (parent 999) is not connected to Electronics, and Home and its children belong to a different tree, so none of them appear here. To see the Home tree, change the anchor to WHERE cat_id = 6.

7. CTE vs Temporary Table

Temporary table example

CREATE TEMPORARY TABLE tmp_avg_ratings AS
SELECT
  restaurant,
  AVG(stars) AS avg_stars,
  COUNT(stars) AS ratings_count
FROM ratings
GROUP BY restaurant;

Output of SELECT * FROM tmp_avg_ratings

Burger Barn's ratings_count is 1 (not 2), because COUNT(stars) ignores the NULL.

When to use which

 Need the result in ONE query only?            -> CTE
 Need it in MANY queries in the same session?  -> Temporary table
 Need to walk a tree or generate a sequence?   -> Recursive CTE

A temporary table is visible only to the session that created it, and it is dropped when that session ends (or earlier with DROP TEMPORARY TABLE tmp_avg_ratings;).

Deep Dive

Internal behavior A non-recursive CTE is similar to a derived table (a subquery in FROM) with a name at the top. The optimizer may run it once and keep the result briefly, or merge it into the main query. A recursive CTE is run in rounds: the anchor first, then the recursive part again and again, each time using only the rows from the last round, until a round gives no new rows.

NULL handling

Performance considerations

• A CTE is not automatically faster than a subquery. It mainly makes code readable.
• Referencing the same CTE more than once can help, because the work is written once and the database may reuse the result.
• Index the join column used in a recursive step (for example, parent_id), so each round can find children quickly.
• Always write a stopping condition. MySQL also has a safety limit: cte_max_recursion_depth (1000 by default). A query that goes deeper stops with an error. This also protects you from loops in data, such as A pointing to B and B pointing back to A.

Rules for recursive CTEs in MySQL

• Use WITH RECURSIVE and UNION ALL (or UNION).
• The anchor part must not refer to the CTE. The recursive part must refer to it.
• In the recursive part, MySQL does not allow things like GROUP BY, aggregate functions such as SUM(), window functions, or DISTINCT.
• The column types come from the anchor query, so make the anchor wide enough for what the recursive part builds.

Database behavior CTEs are supported in MySQL 8.0 and later. A WITH clause can also be used before UPDATE and DELETE statements in MySQL 8. PostgreSQL, SQL Server, and Oracle also support CTEs, with small syntax differences (for example, SQL Server does not use the word RECURSIVE).

Real-world Use Cases

• Step-by-step reports: monthly totals, then growth compared to the previous month, then the top growers (a multi-step pipeline).
• Comparing a group to a baseline: restaurants above the overall average, employees above the department average.
• Organization charts: all people under a manager, at every level (recursive CTE).
• Category and folder trees: list every sub-category of a product category, or every file inside a folder.
• Bill of materials: all parts that make up a product.
• Generating sequences: numbers 1 to N, or a list of dates between two dates (recursive CTE).

Review Tips

Interviewers commonly ask:

• "What is a CTE, and how is it different from a subquery?" — A named, reusable block at the top of a query. A subquery is written inline. A CTE can be used several times and can be recursive.
• "Does a CTE improve performance?" — Not by itself. It improves readability. The optimizer decides how to run it.
• "What is the lifetime of a CTE?" — One statement only.
• "What is the difference between a CTE and a temporary table?" — A CTE is logical and lasts for one query. A temporary table is physical and lasts for the session.
• "What are the two parts of a recursive CTE?" — The anchor query and the recursive query.
• "What happens if a recursive CTE has no stopping condition?" — It keeps going until MySQL stops it with an error at the recursion limit.
• "Why use UNION ALL and not UNION in a recursive CTE?" — UNION ALL keeps every row and avoids the extra work of removing duplicates.
• "How do you generate numbers 1 to N in MySQL?" — A recursive CTE.

Common Mistakes to Avoid

Mistake 1: Writing WITH instead of WITH RECURSIVE

Why it happens: The word RECURSIVE looks optional.

Wrong:

WITH demo AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM demo WHERE n < 5
)
SELECT * FROM demo;

Without RECURSIVE, the CTE cannot refer to itself, and MySQL reports an error.

Correct: WITH RECURSIVE demo AS (...).

Mistake 2: No stopping condition

Why it happens: The anchor and the recursive part feel complete.

Wrong:

SELECT n + 1 FROM demo     -- no WHERE n < 5

The recursion never finds an empty round. MySQL stops it with an error when it reaches the recursion limit.

Correct: Always add a condition that eventually becomes false.

Mistake 3: An off-by-one stopping condition

Why it happens: n <= 5 and n < 5 look similar.

Why it matters: The condition is checked on the previous row. With WHERE n < 5, the row 5 is produced (from 4) and then nothing more. With WHERE n <= 5, the row 6 would also be produced.

Correct: Decide which value should be the last one, and test the condition on the row before it.

Mistake 4: Putting ORDER BY inside the CTE

Why it happens: Sorting early feels logical.

Why it hurts: The final query may reorder or filter the rows, so the early sort is wasted (and the database may ignore it).

Correct: Sort in the final query.

Mistake 5: Trying to use a CTE in a second statement

Why it happens: Forgetting that a CTE lives for one statement.

Wrong:

WITH avg_ratings AS (...) SELECT ... ;
SELECT * FROM avg_ratings;      -- error: avg_ratings does not exist

Correct: Repeat the CTE in the next statement, or use a temporary table if you need it across queries.

Mistake 6: Naming a CTE the same as a real table

Why it happens: Reusing a familiar name like ratings.

Why it hurts: Inside that query, the CTE name hides the real table, which causes confusing results.

Correct: Use a descriptive name that is not an existing table name.

Mistake 7: Expecting a CTE to be faster by default

Why it happens: It looks more "organized".

Correct: Use a CTE for clarity. Check the query plan if speed matters.

Mistake 8: Forgetting that a NULL-ignoring average changes the answer

Why it happens: Burger Barn looks like it has two ratings.

Why it matters: AVG(stars) divides by the number of non-NULL ratings (1), not by the number of rows (2). Results would differ if you calculated SUM(stars) / COUNT(*).

Correct: Decide whether NULL should be ignored or counted as 0, and write it explicitly.

Mistake 9: Joining a one-row CTE without a join condition and forgetting why

Why it happens: CROSS JOIN looks dangerous.

Why it is fine here: Joining with a CTE that has exactly one row adds that one value to every row and does not multiply the rows. It would only be a problem if the CTE could return many rows.

Correct: Use it deliberately for one-row baselines, and check that the CTE really returns one row.

Key Takeaways

• A CTE (Common Table Expression) is a temporary named result set, written at the top of a query with WITH.
• It exists only for the duration of that one statement.
• A CTE always produces a table (rows and columns), even if it holds a single value.
• CTEs turn deeply nested subqueries into clear, ordered steps, and let you reuse a result in the same query.
• Several CTEs can be chained with commas under one WITH, and a later CTE can use an earlier one.
• Put ORDER BY in the final query, not inside the CTE (unless you need it with LIMIT).
• A one-row CTE can be attached to every row using a CROSS JOIN, which is handy for baselines like an overall average.
• A recursive CTE has an anchor query and a recursive query, combined with UNION ALL, and it must have a stopping condition.
• Recursive CTEs handle trees (categories, org charts) and generate sequences.
• A CTE is logical and lasts for one query. A temporary table is physical and lasts for the session.
• A CTE is mainly for readability. It is not automatically faster than a subquery.