Queries inside queries

Sometimes the value you want to filter on is itself the answer to a query. SQL lets you nest one inside another.

A query that produces one value

SELECT name, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

The inner query runs once and produces a single number. Straightforward, and the most common shape.

A query that produces a list

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 5000);

Correlated: the inner query depends on the outer row

SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.amount > 5000
);

This one is different in kind. The inner query references c.id, so conceptually it runs once per customer. A good planner rewrites it, but the naive reading is why correlated subqueries have a reputation for being slow.

EXISTS stops at the first match, so it is usually the right choice over IN when you only care whether any row qualifies rather than which.

The NOT IN trap

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

If any customer_id is NULL, this returns nothing at all. Not an error, not a warning, an empty result. id NOT IN (1, 2, NULL) asks whether id differs from NULL, which is unknown rather than true, so no row qualifies.

NOT EXISTS does not have this behaviour, which is a good reason to prefer it.

Set operations

SELECT city FROM customers
UNION           -- both, duplicates removed
SELECT city FROM suppliers;

UNION ALL keeps duplicates and is faster, because deduplicating requires a sort. INTERSECT keeps rows in both, EXCEPT keeps rows in the first and not the second.