Subqueries & CTEs

Some questions cannot be answered in a single pass: "which products sell above the average price?" needs the average first. Subqueries and Common Table Expressions (CTEs) let you compute an intermediate result and use it in the main query. MySQL supports CTEs from version 8.0.

The examples use a small retail schema: products(id, name, category, price) and order_items(order_id, product_id, qty, unit_price).

Scalar subqueries

A scalar subquery returns exactly one value and can be used wherever a single value fits.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Subqueries with IN and EXISTS

-- Products that have never been ordered
SELECT p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

EXISTS stops at the first matching row, so it is often efficient. Be careful with NOT IN: if the subquery returns even one NULL, NOT IN returns no rows at all. Prefer NOT EXISTS for "missing" checks.

Correlated subqueries

A correlated subquery refers to the outer row, so it conceptually runs once per row.

-- Products priced above their own category's average
SELECT p.name, p.category, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(p2.price) FROM products p2 WHERE p2.category = p.category
);

Derived tables

A subquery in the FROM clause acts like a temporary table and must have an alias.

SELECT category, AVG(revenue) AS avg_product_revenue
FROM (
    SELECT p.category, p.id, SUM(oi.qty * oi.unit_price) AS revenue
    FROM products p JOIN order_items oi ON oi.product_id = p.id
    GROUP BY p.category, p.id
) AS per_product
GROUP BY category;

Common Table Expressions

A CTE gives a name to a subquery with WITH, making long queries read top to bottom like a recipe.

WITH product_revenue AS (
    SELECT product_id, SUM(qty * unit_price) AS revenue
    FROM order_items
    GROUP BY product_id
),
ranked AS (
    SELECT p.name, p.category, pr.revenue
    FROM product_revenue pr JOIN products p ON p.id = pr.product_id
)
SELECT category, COUNT(*) AS products_sold, SUM(revenue) AS category_revenue
FROM ranked
GROUP BY category
ORDER BY category_revenue DESC;

Recursive CTEs

WITH RECURSIVE walks hierarchies such as category trees or reporting lines.

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.level + 1
    FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY level;

Tool	Best for
Scalar subquery	Comparing against one computed value
EXISTS / NOT EXISTS	Presence or absence checks
Derived table	Aggregating an aggregate
CTE	Multi-step logic, reuse, readability
Recursive CTE	Trees and hierarchies

Common mistakes

• Using NOT IN against a column that can contain NULL.
• Forgetting the alias on a derived table (Every derived table must have its own alias).
• A scalar subquery returning more than one row (Subquery returns more than 1 row).
• Writing deeply nested subqueries when a CTE would be far easier to read and debug.