Subqueries, EXISTS and Correlated Queries

Some questions need an answer before they can be asked. "Which products cost more than the average?" needs the average first. "Which customers have never ordered?" needs the list of customers who have. A subquery is a SELECT inside another statement, in brackets, that supplies that first answer. You already used one on the previous page to fix the fan-out trap.

Subqueries are everywhere in data work: keep the rows above a threshold computed from the data itself, filter a training set to users who exist in another table, compare each model's score with its own average. Knowing when a subquery, a join or EXISTS says it most clearly — and the one NULL trap that turns NOT IN into an empty result — is what this page is about.

What you will learn

• Scalar subqueries, and the difference between single-row and multi-row subqueries
• Subqueries in WHERE, FROM (derived tables) and SELECT
• Correlated subqueries: a subquery that runs "per row"
• EXISTS / NOT EXISTS, IN vs EXISTS, and why NOT IN breaks with NULL
• Rewriting subqueries as joins, and choosing the clearest form

Scalar subqueries: one value

A scalar subquery returns exactly one row with one column — a single value — so it can stand wherever a value can. The average product price:

SELECT ROUND(AVG(price), 2) AS avg_price FROM products;

+-----------+
| avg_price |
+-----------+
|   5254.55 |
+-----------+

Put that query in brackets inside WHERE, and the database computes it first, then uses the value:

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

+-----------------------------+-------+
| name                        | price |
+-----------------------------+-------+
| 27-inch Monitor             | 18500 |
| AI Engineering Bootcamp     | 15000 |
| Noise-Cancelling Headphones |  7500 |
+-----------------------------+-------+

You cannot write WHERE price > AVG(price): aggregates are not allowed in WHERE, because WHERE looks at one row at a time (see Aggregates, GROUP BY and HAVING). The subquery is a separate query over the whole table, so it can aggregate. The value is not hard-coded either: add products tomorrow and the threshold moves with the data.

A scalar subquery also works in the SELECT list, as a column:

SELECT name, price,
       price - (SELECT MIN(price) FROM products) AS above_cheapest
FROM products
WHERE category_id = 2
ORDER BY price;

+-----------------------------+-------+----------------+
| name                        | price | above_cheapest |
+-----------------------------+-------+----------------+
| Wireless Mouse              |   900 |              0 |
| Mechanical Keyboard         |  4500 |           3600 |
| Noise-Cancelling Headphones |  7500 |           6600 |
| 27-inch Monitor             | 18500 |          17600 |
+-----------------------------+-------+----------------+

Single-row vs multi-row: = needs one value

=, >, < compare with one value. What if the subquery returns several rows? The three dialects disagree, and SQLite's answer is the dangerous one:

SELECT name FROM customers
WHERE customer_id = (SELECT customer_id FROM orders WHERE status = 'delivered');

+--------------+
| name         |
+--------------+
| Nadia Rahman |
+--------------+

Eleven orders are delivered, but SQLite silently used the first row the subquery produced and ignored the rest. MySQL and PostgreSQL refuse:

-- MySQL
SELECT name FROM customers
WHERE customer_id = (SELECT customer_id FROM orders WHERE status = 'delivered');

Error: ERROR 1242: Subquery returns more than 1 row

-- PostgreSQL
SELECT name FROM customers
WHERE customer_id = (SELECT customer_id FROM orders WHERE status = 'delivered');

Error: more than one row returned by a subquery used as an expression

When a subquery can return many rows, compare with IN, which means "equal to any value in this list":

SELECT name, city
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2026-05-01')
ORDER BY name;

+---------------+------------+
| name          | city       |
+---------------+------------+
| Farhana Akter | Dhaka      |
| Imran Hossain | Khulna     |
| Mitu Das      | Dhaka      |
| Tanvir Ahmed  | Chattogram |
+---------------+------------+

Tanvir and Farhana ordered more than once in that period, but each appears once: IN only asks whether a match exists, it does not multiply rows like a join would.

Subqueries in FROM: derived tables

A subquery in FROM is a temporary table that exists for one query. It is how you aggregate twice — for example, the average order value, which needs order totals first:

SELECT ROUND(AVG(order_total), 2) AS avg_order_value, MAX(order_total) AS biggest_order
FROM (SELECT order_id, SUM(quantity * unit_price) AS order_total
      FROM order_items
      GROUP BY order_id) AS t;

+-----------------+---------------+
| avg_order_value | biggest_order |
+-----------------+---------------+
|         7396.43 |         18500 |
+-----------------+---------------+

AVG(quantity * unit_price) straight over order_items would be the average item value — a different number. Give every derived table an alias (AS t). SQLite and PostgreSQL 16+ accept it without one, but MySQL (and older PostgreSQL) refuse:

-- MySQL
SELECT ROUND(AVG(order_total), 2) AS avg_order_value
FROM (SELECT order_id, SUM(quantity * unit_price) AS order_total
      FROM order_items
      GROUP BY order_id);

Error: ERROR 1248: Every derived table must have its own alias

-- PostgreSQL
SELECT ROUND(AVG(order_total), 2) AS avg_order_value
FROM (SELECT order_id, SUM(quantity * unit_price) AS order_total
      FROM order_items
      GROUP BY order_id);

+-----------------+
| avg_order_value |
+-----------------+
|         7396.43 |
+-----------------+

Correlated subqueries

The subqueries so far ran once, on their own. A correlated subquery refers to a column of the outer query, so its answer is different for each outer row. Which products cost more than the average of their own category?

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

+---------------------------+-------+-------------+
| name                      | price | category_id |
+---------------------------+-------+-------------+
| Hands-On Machine Learning |  2500 |           1 |
| 27-inch Monitor           | 18500 |           2 |
| AI Engineering Bootcamp   | 15000 |           3 |
| USB-C Hub                 |  2200 |           4 |
+---------------------------+-------+-------------+

p.category_id inside the brackets belongs to the outer row. Conceptually the database goes row by row:

Two aliases of the same table (p outside, p2 inside) keep it unambiguous. In an AI setting the same shape answers "which eval runs scored below that model's average?".

A correlated subquery in SELECT computes one value per row — handy for a quick per-customer summary:

SELECT c.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;

+-----------------+--------+------------+
| name            | orders | last_order |
+-----------------+--------+------------+
| Nadia Rahman    |      3 | 2026-04-19 |
| Tanvir Ahmed    |      3 | 2026-05-21 |
| Farhana Akter   |      3 | 2026-06-09 |
| Rafiq Islam     |      1 | 2026-02-25 |
| Sadia Chowdhury |      1 | 2026-03-15 |
| Imran Hossain   |      2 | 2026-06-02 |
| Mitu Das        |      1 | 2026-05-06 |
| Karim Uddin     |      0 | NULL       |
+-----------------+--------+------------+

Karim gets 0 with no LEFT JOIN, and the two subqueries cannot fan out into each other. The cost: conceptually each subquery runs once per customer. Optimizers often rewrite this into a join, but with many subqueries over big tables a single GROUP BY join (or a derived table) is usually faster — check with EXPLAIN, covered in the advanced tutorial.

EXISTS and NOT EXISTS

EXISTS (subquery) is true if the subquery returns at least one row. What it selects does not matter, so people write SELECT 1. It is almost always correlated:

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.customer_id;

+-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           8 | Karim Uddin |
+-------------+-------------+

The same answer as the LEFT JOIN … IS NULL anti-join from LEFT, RIGHT, FULL, CROSS and Self Joins, but it reads like the question: "customers for whom no order exists". The database can stop at the first matching row, so EXISTS is usually cheap. The subquery can be as rich as you need — which products appear in a cancelled order?

SELECT p.name
FROM products p
WHERE EXISTS (SELECT 1
              FROM order_items oi
              JOIN orders o ON o.order_id = oi.order_id
              WHERE oi.product_id = p.product_id AND o.status = 'cancelled');

+-----------------+
| name            |
+-----------------+
| 27-inch Monitor |
+-----------------+

IN vs EXISTS, and the NOT IN trap

For "has a match", IN and EXISTS give the same rows, and modern optimizers usually run them the same way; choose the one that reads better. For "has no match" they differ, because of NULL. Which categories have no products?

SELECT name FROM categories
WHERE category_id NOT IN (SELECT category_id FROM products);

+------+
| name |
+------+
+------+

Nothing — yet Furniture has no products. The subquery's list contains the Gift Card's NULL. 5 NOT IN (1, 2, 3, 4, NULL) means 5 <> 1 AND … AND 5 <> NULL, and 5 <> NULL is unknown, so the whole test is never true, for any category. One NULL in the list empties the result. NOT EXISTS does not have this problem, because it only asks whether a matching row exists:

SELECT name FROM categories c
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category_id = c.category_id);

+-----------+
| name      |
+-----------+
| Furniture |
+-----------+

All three dialects behave the same here; this is standard SQL logic, not a bug. If you do use NOT IN with a subquery, filter the NULLs out: … NOT IN (SELECT category_id FROM products WHERE category_id IS NOT NULL). Better habit: use NOT EXISTS.

Subquery or join?

Many subqueries can be written as joins and the other way round. They are not always identical. Dhaka customers who have ordered, as a join:

SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.city = 'Dhaka'
ORDER BY c.name;

+---------------+
| name          |
+---------------+
| Farhana Akter |
| Farhana Akter |
| Farhana Akter |
| Mitu Das      |
| Nadia Rahman  |
| Nadia Rahman  |
| Nadia Rahman  |
+---------------+

The join returns one row per order. You could add DISTINCT, but that hides the fact that you joined at the wrong grain. EXISTS keeps the grain at one row per customer:

SELECT c.name
FROM customers c
WHERE c.city = 'Dhaka'
  AND EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.name;

+---------------+
| name          |
+---------------+
| Farhana Akter |
| Mitu Das      |
| Nadia Rahman  |
+---------------+

Speed differences between these forms are usually small on modern databases; write the clearest one first and measure only when it is slow.

Common mistakes

• = with a subquery that can return several rows. SQLite silently takes the first row; MySQL and PostgreSQL raise an error. Fix: use IN, or make the subquery return one row on purpose (an aggregate, or a filter on a key).
• NOT IN over a column that can be NULL. One NULL makes the result empty. Fix: NOT EXISTS, or add WHERE col IS NOT NULL inside the subquery.
• Forgetting the correlation. WHERE price > (SELECT AVG(price) FROM products p2) without WHERE p2.category_id = p.category_id compares with the overall average, with no error. Fix: alias both copies of the table and check that the inner WHERE mentions the outer alias.
• A derived table without an alias. Works in SQLite, fails in MySQL with ERROR 1248. Fix: always write (…) AS t.
• Using a join to filter, then DISTINCT to clean up. It hides the grain problem and can merge rows that really are different. Fix: filter with EXISTS when you need no columns from the other table.

Try it yourself

1. Easy: List the products cheaper than the average product price, cheapest first (ties by name).
2. Medium: With a correlated subquery, find the highest-paid employee in each department: name, salary, department, sorted by department.
3. Hard: Find the customers whose total spending on non-cancelled orders is above the average of that total over customers who have ordered. Show name and amount, biggest first. (Hint: a derived table of per-customer totals, used twice.)

Answers

-- 1
SELECT name, price
FROM products
WHERE price < (SELECT AVG(price) FROM products)
ORDER BY price, name;

-- 2
SELECT name, salary, department
FROM employees e
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department = e.department)
ORDER BY department;

+-----------------+--------+-------------+
| name            | salary | department  |
+-----------------+--------+-------------+
| Lamia Karim     | 160000 | Data        |
| Hasan Mahmud    | 180000 | Engineering |
| Ayesha Siddiqua | 250000 | Management  |
+-----------------+--------+-------------+

-- 3
SELECT c.name, t.spent
FROM (SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent
      FROM orders o
      JOIN order_items oi ON oi.order_id = o.order_id
      WHERE o.status <> 'cancelled'
      GROUP BY o.customer_id) AS t
JOIN customers c ON c.customer_id = t.customer_id
WHERE t.spent > (SELECT AVG(spent)
                 FROM (SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent
                       FROM orders o
                       JOIN order_items oi ON oi.order_id = o.order_id
                       WHERE o.status <> 'cancelled'
                       GROUP BY o.customer_id) AS t2)
ORDER BY t.spent DESC;

+---------------+-------+
| name          | spent |
+---------------+-------+
| Nadia Rahman  | 23600 |
| Tanvir Ahmed  | 22500 |
| Imran Hossain | 19950 |
+---------------+-------+

The average is 14,175 over the six customers who ordered. Writing the same derived table twice is clumsy; a CTE (WITH, taught in CTEs and Recursive Queries, the first page of the advanced tutorial) names it once and reuses it.

Summary

• A subquery is a SELECT in brackets that supplies a value, a list or a table to the outer query.
• Scalar subqueries go where a single value goes; if one returns several rows, SQLite quietly uses the first while MySQL and PostgreSQL raise an error — use IN for lists.
• Derived tables in FROM let you aggregate twice; always give them an alias (MySQL requires it).
• Correlated subqueries refer to the outer row and answer "compared with its own group" questions.
• EXISTS/NOT EXISTS test for a matching row without changing the grain; NOT IN returns nothing if the list contains a NULL.

Next: UNION, INTERSECT and EXCEPT combines whole query results instead of nesting them: stacking rows from two queries, and finding the rows two results share or do not share.