ASAmol Shukla

9. Subqueries and CTEs

Step 1 of 4 · Lesson

Lesson 9 of 21

MySQL subqueries, EXISTS and CTEs (WITH) step by step

Use a query inside a query: scalar subqueries, IN and EXISTS / NOT EXISTS, derived tables, and readable multi-step reports with WITH (common table expressions).

85 min 4 min read 19 problems · 0 solved Learning Shop (small)

In short

What is a CTE in MySQL?

A CTE (common table expression) is a named, temporary result defined with WITH name AS (SELECT …) at the start of a query. The main query can then read it like a table. It exists only while that one statement runs, and it makes multi-step logic readable. MySQL supports CTEs from version 8.0.

By the end of this lesson you can

  • Compare each row with a single calculated value using a scalar subquery
  • Filter with IN (subquery), EXISTS and NOT EXISTS — and know the NOT IN + NULL trap
  • Aggregate first in a derived table or CTE, then join or filter
  • Chain several CTEs into a readable, reviewable report

A query inside a query

A subquery is a SELECT in brackets used inside another statement. The simplest kind returns exactly one value — a scalar subquery — and can stand anywhere a number could:

SQL · shop
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;
MySQL result2 rows
product_nameprice
Keyboard1200.00
Desk lamp800.00

MySQL runs the inner query first (average = 445), then compares every product with it. You cannot write WHERE price > AVG(price) directly — aggregates are not allowed in WHERE (lesson 7).

IN with a subquery

When the inner query returns a list of values, use IN. Which customers have at least one completed order?

SQL · shop
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE status = 'completed'
)
ORDER BY customer_id;
MySQL result3 rows
customer_idcustomer_name
1Asha
2Bilal
4Dev

EXISTS and NOT EXISTS

EXISTS (subquery) is true when the subquery returns at least one row. The subquery usually refers to the outer row — that makes it a correlated subquery, re-evaluated per row. NOT EXISTS is the safe, readable way to ask which rows have no match?:

SQL · shop
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items i
  JOIN orders o ON o.order_id = i.order_id
  WHERE i.product_id = p.product_id
    AND o.status = 'completed'
)
ORDER BY p.product_id;
MySQL result1 row
product_idproduct_name
6Planner

Planner was only ordered in pending order 107, so it has never been sold. SELECT 1 is a convention: EXISTS only cares whether a row exists, not what it contains.

Derived tables: aggregate first, then use the result

A subquery in FROM is a derived table. It is how you fix the grain problem from lesson 8: first build one row per order, then analyse those totals.

SQL · shop
SELECT ROUND(AVG(t.total), 2) AS average_order_value
FROM (
  SELECT o.order_id, SUM(i.quantity * i.unit_price) AS total
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.order_id
) AS t;
MySQL result1 row
average_order_value
1025.00

6150 ÷ 6 orders = 1025.00. Averaging the item lines instead (6150 ÷ 12) would give 512.50 — a different, wrong answer to what is the average order worth?. A derived table must have an alias (AS t).

CTEs: name each step with WITH

A CTE does the same as a derived table but puts the step first and gives it a name, so the query reads top to bottom like a recipe:

WITH step_name AS (
  SELECT …
)
SELECT … FROM step_name;

The CTE exists only while this one statement runs. It is not saved — that is what views are for (lesson 14).

SQL · shop
WITH order_totals AS (
  SELECT o.order_id, o.customer_id, SUM(i.quantity * i.unit_price) AS total
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.order_id, o.customer_id
)
SELECT order_id, total
FROM order_totals
WHERE total > (SELECT AVG(total) FROM order_totals)
ORDER BY order_id;
MySQL result4 rows
order_idtotal
1021300.00
1031400.00
1061300.00
1081300.00

Orders above the 1025 average: 102, 103, 106 and 108. Notice the CTE is used twice — in FROM and inside the scalar subquery.

Chain CTEs for a full report

Separate several CTEs with commas. Each one can read the ones before it. Here: revenue per customer, then every customer — including those with no completed orders — shown with 0:

SQL · shop
WITH order_totals AS (
  SELECT o.order_id, o.customer_id, SUM(i.quantity * i.unit_price) AS total
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.order_id, o.customer_id
),
customer_revenue AS (
  SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
  FROM order_totals
  GROUP BY customer_id
)
SELECT c.customer_name,
       COALESCE(r.orders, 0)  AS orders,
       COALESCE(r.revenue, 0) AS revenue
FROM customers c
LEFT JOIN customer_revenue r ON r.customer_id = c.customer_id
ORDER BY revenue DESC, c.customer_id;
MySQL result6 rows
customer_nameordersrevenue
Bilal22600.00
Dev21850.00
Asha21700.00
Charu00.00
Esha00.00
Farah00.00

The revenue column sums to 6150, the same control total as lesson 8. COALESCE(…, 0) turns no data (NULL from the LEFT JOIN) into a real zero for the report.

Subqueries in the SELECT list

A scalar subquery can also produce a column. When it refers to the outer row it is correlated: MySQL runs it once per row. It reads naturally for small lookups — how many orders does each customer have? — and keeps customers with zero orders without a LEFT JOIN:

SQL · shop
SELECT c.customer_name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders,
       (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c
ORDER BY c.customer_id;
MySQL result6 rows
customer_nameorderslast_order
Asha22026-02-03
Bilal22026-03-02
Charu12026-02-10
Dev22026-03-11
Esha12026-03-04
Farah0NULL

Farah shows 0 orders and a NULL last order. On very large tables a correlated subquery per column can be slow; a grouped CTE joined once is usually faster and easier to extend.

Common mistakes

Wrong

WHERE price > AVG(price)

Fix

WHERE price > (SELECT AVG(price) FROM products)

Why: Aggregates are not allowed in WHERE (error 1111).

Wrong

WHERE customer_id NOT IN (SELECT referrer_id FROM …)  -- column has NULLs

Fix

WHERE NOT EXISTS (SELECT 1 FROM … WHERE referrer_id = c.customer_id)

Why: One NULL in the list makes NOT IN unknown for every row, so nothing is returned.

Wrong

Averaging order_items rows to get the average order value

Fix

Aggregate to one row per order in a CTE, then AVG.

Why: That is the average item line (512.50), not the average order (1025.00).

Wrong

FROM (SELECT …)  -- no alias

Fix

FROM (SELECT …) AS t

Why: Every derived table needs a name (error 1248).

Check yourself

  1. When does a subquery have to return exactly one value, and what happens if it returns two?
  2. Why is NOT EXISTS safer than NOT IN?
  3. What is the difference between a CTE and a view?

Answer out loud or in your notebook. If one is hard, reread that section before practising.

Frequently asked questions

Does MySQL support CTEs (WITH clause)?

Yes, from MySQL 8.0, including recursive CTEs (WITH RECURSIVE). MySQL 5.7 does not support them.

Is a CTE faster than a subquery?

Usually they perform the same: MySQL can merge both into the outer query. Choose CTEs for readability and when one step is reused.

When should I use EXISTS instead of IN?

Use EXISTS / NOT EXISTS when checking whether related rows exist, especially for 'no match' questions, because NOT IN breaks when the subquery returns NULL.

Quick quiz — 4 questions

Quick Check: Subqueries and CTEs

1 / 4
What must a scalar subquery return?

Answers — Subqueries and CTEs

  1. Easy. Find products priced above the catalogue average. Target: Desk lamp and Keyboard; average price is 445.

    SELECT product_name FROM products WHERE price > (SELECT AVG(price) FROM products) ORDER BY product_id;

  2. Medium. Use a CTE to calculate completed revenue per customer. Target: Asha=1700, Bilal=2600, Dev=1850.

    WITH spend AS (SELECT o.customer_id, SUM(i.quantity*i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id=o.order_id WHERE o.status='completed' GROUP BY o.customer_id) SELECT c.customer_name,s.revenue FROM spend s JOIN customers c ON c.customer_id=s.customer_id ORDER BY c.customer_id;

  3. Hard. Find completed orders above average completed order value. Target: 102,103,106,108. Compare against 1025, not an item average.

    WITH totals AS (SELECT o.order_id,SUM(i.quantity*i.unit_price) AS total FROM orders o JOIN order_items i ON i.order_id=o.order_id WHERE o.status='completed' GROUP BY o.order_id) SELECT order_id,total FROM totals WHERE total > (SELECT AVG(total) FROM totals) ORDER BY order_id;

  4. Professional. Include zero-spend customers in a customer report. Target: All 6 customers; Charu, Esha, Farah have 0 completed revenue. Total remains 6150.

    WITH spend AS (SELECT o.customer_id,SUM(i.quantity*i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id=o.order_id WHERE o.status='completed' GROUP BY o.customer_id) SELECT c.customer_id,c.customer_name,COALESCE(s.revenue,0) AS revenue FROM customers c LEFT JOIN spend s ON s.customer_id=c.customer_id ORDER BY c.customer_id;

  5. Easy. Find the most expensive product using a subquery. Target: 1 row: Keyboard, 1200.00

    SELECT product_name, price FROM products WHERE price = (SELECT MAX(price) FROM products);

  6. Easy. Find the cheapest product using a subquery. Target: 1 row: Pen, 20.00

    SELECT product_name, price FROM products WHERE price = (SELECT MIN(price) FROM products);

  7. Easy. List customers who have placed at least one order. Target: 5 rows: Asha, Bilal, Charu, Dev, Esha

    SELECT customer_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders) ORDER BY customer_id;

  8. Easy. List customers who have never ordered, using NOT IN. Target: 1 row: Farah

    SELECT customer_name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);

  9. Medium. List products that appear in any completed order. Target: 5 rows: Notebook, Pen, Desk lamp, Mug, Keyboard

    SELECT product_name FROM products WHERE product_id IN (SELECT i.product_id FROM order_items i JOIN orders o ON o.order_id = i.order_id WHERE o.status = 'completed') ORDER BY product_id;

  10. Medium. Use EXISTS to list customers with a completed order. Target: 3 rows: Asha, Bilal, Dev

    SELECT c.customer_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'completed') ORDER BY c.customer_id;

  11. Medium. Show each product with the overall average price next to it. Target: 6 rows; avg_price is 445 on every row

    SELECT product_name, price, (SELECT AVG(price) FROM products) AS avg_price FROM products ORDER BY product_id;

  12. Medium. Use a CTE to get revenue per completed order, then the biggest order. Target: 1 row: 103, 1400.00

    WITH t AS (SELECT o.order_id, SUM(i.quantity * i.unit_price) AS total FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.order_id) SELECT order_id, total FROM t ORDER BY total DESC LIMIT 1;

  13. Medium. Use a CTE to get units per product, then list products that sold more than 2 units. Target: 4 rows: Pen 15, Notebook 6, Mug 5, Desk lamp 3

    WITH u AS (SELECT product_id, SUM(quantity) AS units FROM order_items GROUP BY product_id) SELECT p.product_name, u.units FROM u JOIN products p ON p.product_id = u.product_id WHERE u.units > 2 ORDER BY u.units DESC;

  14. Hard. Show each product with the average price of its own category. Target: 6 rows; Stationery avg 140, Electronics 1000, Home 250

    SELECT p.product_name, p.category, p.price, (SELECT AVG(p2.price) FROM products p2 WHERE p2.category = p.category) AS category_avg FROM products p ORDER BY p.product_id;

  15. Hard. List products priced above their category average. Target: 2 rows: Keyboard 1200, Planner 300

    SELECT product_name, category, price FROM products p WHERE price > (SELECT AVG(price) FROM products p2 WHERE p2.category = p.category) ORDER BY product_id;

  16. Hard. Find the customer with the highest completed revenue using two CTEs. Target: 1 row: Bilal, 2600.00

    WITH spend AS (SELECT o.customer_id, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.customer_id), top AS (SELECT MAX(revenue) AS m FROM spend) SELECT c.customer_name, s.revenue FROM spend s JOIN top ON s.revenue = top.m JOIN customers c ON c.customer_id = s.customer_id;

  17. Hard. Show each completed order's share of total completed revenue as a percentage (2 decimals). Target: 6 rows: 101 4.88, 102 21.14, 103 22.76, 105 8.94, 106 21.14, 108 21.14

    WITH t AS (SELECT o.order_id, SUM(i.quantity * i.unit_price) AS total FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.order_id) SELECT order_id, total, ROUND(100 * total / (SELECT SUM(total) FROM t), 2) AS pct FROM t ORDER BY order_id;

  18. Professional. Customer segment report: label each customer 'none', 'low' (< 1800) or 'high' (>= 1800) by completed revenue, and count per segment. Target: 3 rows: high 2, low 1, none 3

    WITH spend AS (SELECT o.customer_id, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.customer_id), seg AS (SELECT c.customer_id, CASE WHEN s.revenue IS NULL THEN 'none' WHEN s.revenue < 1800 THEN 'low' ELSE 'high' END AS segment FROM customers c LEFT JOIN spend s ON s.customer_id = c.customer_id) SELECT segment, COUNT(*) AS customers FROM seg GROUP BY segment ORDER BY segment;

  19. Professional. Category report: completed revenue per category and its share of the 6150 total, largest first. Target: 3 rows: Electronics 4000 (65.04), Home 1250 (20.33), Stationery 900 (14.63)

    WITH r AS (SELECT p.category, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id JOIN products p ON p.product_id = i.product_id WHERE o.status = 'completed' GROUP BY p.category) SELECT category, revenue, ROUND(100 * revenue / (SELECT SUM(revenue) FROM r), 2) AS pct FROM r ORDER BY revenue DESC;

Up next · Lesson 10

UNION and set thinking

75 min · 16 practice problems

Start next lesson
Joins and grain UNION and set thinking