Subqueries

A subquery is just a SELECT nested inside another SELECT. The whole skill is recognising where to put it — each position has its own job. We will use these two tables:

products	category	price
Pencil	Stationery	2
Notebook	Stationery	6
Coffee	Grocery	9
Laptop	Electronics	900
Cable	Electronics	12

orders	user_id
101	1
102	1
103	3

In WHERE — a value, a set, or a truth check

A scalar subquery returns exactly one value, usable anywhere a value goes. It runs once, and the outer query compares each row to it:

SELECT name, price
FROM   products
WHERE  price > (SELECT AVG(price) FROM products);   -- average is 185.8

name	price
Laptop	900

IN (subquery) checks membership in the set the subquery returns; EXISTS (subquery) ignores what the subquery returns and asks only whether it returns any row. EXISTS is the NULL-safe favourite — “users who have placed at least one order”:

SELECT u.name
FROM   users u
WHERE  EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

That o.user_id = u.id references a column from the outer query, which makes it a correlated subquery — one that logically re-runs for every outer row, rather than once. It is the source of both its power and its cost.

In SELECT — annotate each row (the slow way)

A scalar subquery in SELECT computes a per-row value:

SELECT u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM   users u
ORDER BY order_count DESC;

This works, but a correlated subquery in SELECT fires a fresh scan for every row — O(n²) in the worst case, fine on small data and ruinous on a 100-million-row table. The senior move is to spot the pattern (“a per-row aggregate over a related set”) and rewrite it as a window function: COUNT(*) OVER (PARTITION BY user_id) does the same job in a single pass.

In FROM — the derived table

When you need to aggregate first and then compare, wrap the aggregate in a subquery, alias it, and treat it like a table. “Products priced above the average for their own category”:

SELECT p.name, p.category, p.price, c.avg_price
FROM   products p
JOIN  (SELECT category, AVG(price) AS avg_price
       FROM products GROUP BY category) c
  ON   c.category = p.category
WHERE  p.price > c.avg_price;

name	category	price	avg_price
Notebook	Stationery	6	4
Laptop	Electronics	900	456

The inner query collapses to one row per category (Stationery 4, Grocery 9, Electronics 456); the outer joins each product to its category’s average and keeps the ones above it. That “aggregate, then compare back” shape is the skeleton of most non-trivial analytics SQL.

Use a subquery when…	Use a JOIN when…
Filtering by membership (IN/EXISTS)	You need columns from both sides
Comparing to a single aggregate	You’re combining rows row-for-row

Practice

Quick check

0/3

Q1Which form returns no rows if the inner result contains a NULL?

WHERE x IN (subquery)WHERE x NOT IN (subquery)WHERE EXISTS (subquery)WHERE NOT EXISTS (subquery)

Check answer

Q2What makes a subquery 'correlated'?

It uses an aggregateIt references a column from the outer queryIt appears in WHEREIt returns multiple rows

Check answer

Q3You want each product's price compared to its own category's average. Cleanest pattern?

A scalar subquery in SELECT that re-aggregates per category each rowA derived table that aggregates per category, joined back to productsA nested subquery inside WHERE per categoryThree separate queries

Check answer