Subqueries and CTEs

A query inside a query#

A subquery is a SELECT used as a value, a list, or a table.

-- scalar subquery: one value
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- list subquery
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'paid');

-- derived table
SELECT t.customer_id, t.spent
FROM (SELECT o.customer_id, SUM(oi.qty * oi.unit_price) AS spent
      FROM orders o JOIN order_items oi ON oi.order_id = o.id
      GROUP BY o.customer_id) AS t
WHERE t.spent > 500;

A derived table needs an alias, always.

Correlated subqueries#

A correlated subquery references the outer query, so conceptually it runs once per outer row.

SELECT p.name, p.price
FROM products p
WHERE p.price > (SELECT AVG(p2.price) FROM products p2
                 WHERE p2.category = p.category);

"Products priced above their own category's average." The inner query changes based on the current outer row. Modern MySQL optimizes many of these, but when a query is slow, a correlated subquery is the first suspect.

EXISTS versus IN#

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

EXISTS stops at the first match and only tests existence. What you select inside doesn't matter, hence SELECT 1.

The NOT IN trap deserves its own warning:

WHERE id NOT IN (SELECT customer_id FROM orders)

If that subquery returns even one NULL, NOT IN returns no rows at all, because every comparison becomes unknown. NOT EXISTS doesn't have that problem. Prefer it.

CTEs: giving the subquery a name#

WITH customer_spend AS (
  SELECT o.customer_id, SUM(oi.qty * oi.unit_price) AS spent
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
),
avg_spend AS (
  SELECT AVG(spent) AS avg_spent FROM customer_spend
)
SELECT c.name, cs.spent
FROM customer_spend cs
JOIN avg_spend a ON cs.spent > a.avg_spent
JOIN customers c ON c.id = cs.customer_id;

The logic reads top to bottom, each step named. A CTE can reference earlier CTEs, and can be reused within the same statement.

Recursive CTEs#

WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 5
)
SELECT n FROM nums;

The first part seeds, the second part repeats against the previous output until it returns nothing. This is how you walk org charts and category trees. Always include a stopping condition, or the server stops it at cte_max_recursion_depth (1000 by default).

Common mistakes#

• NOT IN against a column that can be NULL.
• Using a correlated subquery over a huge table where a join plus GROUP BY would do.
• Forgetting the alias on a derived table.
• Believing a CTE is always materialized once. The optimizer decides; don't use it as a performance trick.

Practice#

Find customers who have never placed an order using NOT EXISTS. Then, using a CTE, list each customer's total spend alongside the percentage of the grand total they represent. Finally, generate dates for the next 7 days with a recursive CTE.