Subqueries, EXISTS and CTEs: Queries Inside Queries

Sometimes the answer to one question depends on the answer to another. Which products cost more than the average price? To know that, you first need the average. SQL lets you put one query inside another. It is called a subquery. In this lesson you learn three tools that solve this kind of problem: subqueries, EXISTS and the WITH clause, also called a common table expression or CTE.

A subquery that returns one value

Put a query in brackets where a single value is expected.

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

name         | price
Desk lamp    | 650
Mouse        | 700
Backpack     | 1400
Laptop stand | 1800

The inner query runs first and returns one number, 638.75. Then the outer query uses it like a normal value. If the inner query returned more than one row here, you would get an error or a wrong answer, so make sure it returns exactly one value.

A subquery that returns a list

When the inner query returns a column of values, use it with IN.

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'shipped');

name
Asha Rao
Emma Wilson

The inner query lists the customer ids that have a shipped order. The outer query finds the names that go with those ids. The same result can be written with a join. Joins and subqueries are often two ways to say the same thing. Choose the one that is easier to read.

The NOT IN trap with NULL

NOT IN finds rows whose value is not in a list. It has a serious catch. If the list contains even one NULL, NOT IN returns no rows at all.

-- This returns Hari Singh, because customer_id is never NULL in orders:
SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- But this returns nothing at all:
SELECT name FROM customers WHERE id NOT IN (1, 2, NULL);

The reason is the same as with = NULL. For a customer with id 5, the test is “5 is not 1 and not 2 and not NULL”. The last part is unknown, so the whole test is not definitely true and the row is dropped. If a column in your data can be NULL, use NOT EXISTS or a LEFT JOIN instead.

EXISTS: does a matching row exist?

EXISTS is true when the inner query returns at least one row. It is often written with a link back to the outer row. This is called a correlated subquery, because the inner query uses a value from the outer query.

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

name
Hari Singh

For each customer, the database asks: is there any order for this customer? SELECT 1 simply means we do not care about the column, only whether a row exists. Use EXISTS for “has at least one” and NOT EXISTS for “has none”. It is safe with NULLs and stops looking as soon as it finds one row, which is good for speed.

A subquery in FROM

You can also treat a subquery as a temporary table. It must have a name.

SELECT category, avg_price
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
) AS cat
WHERE avg_price > 500;

This works, but deeper nesting quickly becomes hard to read. That is where CTEs help.

WITH: name each step (CTE)

A CTE gives a name to a query so you can use it like a table in the main query. It reads from top to bottom like a recipe: first do this, then do that.

WITH totals AS (
  SELECT o.customer_id, SUM(p.price * oi.quantity) AS spent
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.id
  JOIN products AS p     ON p.id = oi.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.customer_id
)
SELECT c.name, t.spent
FROM totals AS t
JOIN customers AS c ON c.id = t.customer_id
WHERE t.spent > 1000
ORDER BY t.spent DESC;

name       | spent
Asha Rao   | 4020
Ben Carter | 3340
Divya Nair | 1200

The first part, totals, works out what each customer spent. The second part uses it. You can write several CTEs one after another, separated by commas, and later ones can use earlier ones. CTEs work in SQLite, MySQL 8 and later, PostgreSQL, SQL Server and Oracle. Older MySQL versions do not have them.

Which one should I use?

• A single value to compare with: scalar subquery, like price > (SELECT AVG(price) ...).
• A list to test membership: IN. Mind NULLs with NOT IN.
• “Has at least one” or “has none”: EXISTS or NOT EXISTS, or a LEFT JOIN ... IS NULL.
• A long query with several steps: a CTE, so each step has a clear name.

Your task

1. Find the products priced below the average price. Four products qualify: Notebook, Pen pack, Sticky notes and Water bottle.
2. List customers who have at least one delivered order, using IN, then again using EXISTS. Both should return Asha, Ben, Divya and Farid.
3. Using a CTE, work out the number of items in each order, then show only orders with more than 4 items.
4. Explain in your own words why id NOT IN (1, 2, NULL) returns nothing.

How to check it worked

• The below-average list has four products.
• Both delivered-order queries give the same four names.
• The CTE query returns orders 1 (5 items), 5 (7 items) and 7 (12 items).