Subqueries

Queries so far in the SQL for Beginners series have read from a fixed list of tables — a FROM clause you can hold in your head at a glance. Chapter 13 of this 24-chapter series nests a query inside another one: the subquery (also called an inner query, or a nested SELECT). Subqueries answer questions that are awkward to phrase any other way: “customers who have never ordered”, “orders worth more than the average line”, “every product beside the shop’s maximum price”.

What a subquery is

A subquery is a complete SELECT wrapped in parentheses and used where a value, a list or a table would otherwise appear. The placement decides what the subquery has to produce:

• In the SELECT list — one extra value per row; it must return exactly one row and one column.
• In WHERE or HAVING — a set or a single value that every outer row is tested against.
• In FROM — a whole temporary table (a derived table) that the outer query reads like any other.

Two scalar subqueries in one SELECT show the simplest shape: each returns a single value, so the whole query is one row:

SELECT (SELECT COUNT(*) FROM customers) AS customers,
       (SELECT COUNT(*) FROM orders) AS orders;

Expected output:

customers | orders
6 | 6

Neither inner query mentions an outer table, which makes them uncorrelated — the engine could run them alone, in either order, and get the same two numbers. That independence is what makes scalar subqueries cheap enough to sprinkle through a report.

Filtering with IN and NOT IN

The most common subquery produces a set for the outer query to test membership against. “Which customers have placed an order?” is really a join in disguise, but IN says it in one line:

SELECT first_name, last_name
FROM customers
WHERE id IN (SELECT customer_id FROM orders)
ORDER BY id;

Expected output:

first_name | last_name
Aarav | Sharma
Priya | Nair
Lena | Fischer
Wei | Ling
Amara | Ochieng

Flip the operator to find the gaps — ids in customers that never appear in orders:

SELECT first_name, last_name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Expected output: one row, Marc | Tremblay — customer 4 has signed up but never bought anything. Both forms run the inner query once, collect the ids, then probe that set, which is why IN is both clearer and better planned than a chain of OR clauses. Chapter 12’s Multi-Table Joins and Aliases would produce the same five names, just with more ceremony.

EXISTS: does any row come back?

EXISTS asks a different question. Instead of “is this value in the set?” it asks “does at least one matching row exist?”. The subquery selects anything — 1 by convention — and the engine stops at the first match rather than building a set:

SELECT c.first_name, c.last_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    WHERE o.customer_id = c.id
      AND p.category = 'Tech'
)
ORDER BY c.id;

Expected output:

first_name | last_name
Aarav | Sharma
Priya | Nair
Amara | Ochieng

This one is correlated: o.customer_id = c.id ties each run of the inner query to the customers row currently being considered. Tech titles are products 3 and 4, which appear in orders 2, 3 and 6 — placed by Aarav, Priya and Amara. Note that Marc is absent again, for a completely different reason than Lena and Wei: he has no orders at all.

Because the probe stops at the first match, EXISTS never reads the rest of the inner result — a structural advantage over IN, which has to build its whole set before any testing can begin.

Correlated values in the SELECT list

The same idea adds a computed column without collapsing any rows — something a GROUP BY would need a joined subquery to achieve:

SELECT first_name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders_placed
FROM customers c
ORDER BY c.id;

Expected output:

first_name | orders_placed
Aarav | 2
Priya | 1
Lena | 1
Marc | 0
Wei | 1
Amara | 1

The alias c on the outer table and the qualification o.customer_id = c.id are load-bearing: drop the correlation and the inner query runs once against the whole table, returning 6 for every row. Two edge cases matter whenever a subquery sits in a scalar position (compared with =): if it returns no rows, the comparison becomes NULL and nothing matches — a silent empty result rather than an error. If it returns several rows, SQLite quietly uses one of them (WHERE id = (SELECT customer_id FROM orders) happens to return Aarav), while PostgreSQL and MySQL both refuse with an error. When you cannot promise exactly one row, reach for IN.

Subqueries in FROM: derived tables

Place the subquery in FROM and it becomes a table with a name. The inner query below sums each order’s items; the outer query joins that summary to orders:

SELECT o.id, o.status, sub.items_total
FROM orders o
JOIN (
    SELECT order_id, CAST(SUM(quantity * unit_price) AS INTEGER) AS items_total
    FROM order_items
    GROUP BY order_id
) sub ON sub.order_id = o.id
ORDER BY o.id;

Expected output:

id | status | items_total
1 | paid | 997
2 | shipped | 899
3 | cancelled | 749
4 | shipped | 1347
5 | paid | 649
6 | paid | 1798

The derived table sub has exactly one row per order, so the join cannot multiply rows — the classic double-count bug is avoided by construction. Always alias a derived table: SQLite lets you skip it, but MySQL and PostgreSQL both reject an unnamed subquery in FROM.

Scalar subqueries beside aggregates

HAVING is where a scalar subquery earns its keep, filtering groups against a value computed from the whole table — here, orders whose total beats the average order line (6439 in revenue across 8 lines, an average of 804.875):

SELECT o.id, CAST(SUM(oi.quantity * oi.unit_price) AS INTEGER) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id
HAVING total > (SELECT AVG(quantity * unit_price) FROM order_items)
ORDER BY o.id;

Expected output:

id | total
1 | 997
2 | 899
4 | 1347
6 | 1798

Orders 3 and 5 are missing, at 749 and 649 — the only two under the average. The same trick turns raw totals into shares of the whole, computed once and divided per row:

SELECT o.id,
       CAST(SUM(oi.quantity * oi.unit_price) AS INTEGER) AS total,
       printf('%.1f%%', 100.0 * SUM(oi.quantity * oi.unit_price)
                            / (SELECT SUM(quantity * unit_price) FROM order_items)) AS share
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id
ORDER BY o.id;

Expected output:

id | total | share
1 | 997 | 15.5%
2 | 899 | 14.0%
3 | 749 | 11.6%
4 | 1347 | 20.9%
5 | 649 | 10.1%
6 | 1798 | 27.9%

Six independent divisions, one denominator, and the shares add up to 100%. That is the subquery’s real job: compute a context once, then hand every row the same context.

Common mistakes

• Leaving off the parentheses — a subquery is a complete SELECT and must be wrapped; WHERE id IN SELECT ... is a syntax error in every dialect.
• Comparing with = against a multi-row subquery — SQLite silently picks a row while PostgreSQL and MySQL raise an error, so the same query means different things in different places; use IN.
• NOT IN against a subquery that can return NULL — one null makes every comparison unknown and the filter matches nothing at all; prefer NOT EXISTS, or exclude nulls inside the subquery.
• Forgetting the correlation — a missing o.customer_id = c.id turns a per-row lookup into one query over the entire table, quietly returning the same number for every row.
• Nesting more than two or three levels — each layer must be parsed as a whole, and nobody can read it; name the logic with a join instead, or wait two chapters for common table expressions.

Practice checklist

1. Rewrite the IN query for customers as an EXISTS query and confirm you get the same five names in the same order.
2. Run SELECT name FROM products WHERE id NOT IN (SELECT product_id FROM order_items) — expect zero rows, then prove your understanding by inserting a seventh product and watching it appear.
3. Run the HAVING query, work out the average line value by hand, and explain in one sentence why orders 3 and 5 are excluded.
4. Convert the derived-table query into a plain join with GROUP BY order_id, and check that both print the same six totals.
5. Reproduce the null trap with WHERE id NOT IN (SELECT NULL FROM orders), then rewrite it as NOT EXISTS and get Marc back.

What's next?

Subqueries let one query answer for another, but they still compare row by row. Next, compare whole result sets directly — stacking, intersecting and subtracting them with Set Operations and EXISTS.