Scalar and column subqueries

Subqueries in SELECT and WHERE, and correlated versus uncorrelated.

What you'll be able to do

Use a subquery in WHERE and SELECT
Distinguish correlated from uncorrelated
Predict the performance cost

In the WHERE clause

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

A scalar subquery returns exactly one value. This one is uncorrelated — it does not reference the outer query, so it runs once and the result is reused.

Returning a list

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total > 500);

A column subquery returns one column of many rows, used with IN, ANY or ALL.

Remember the null trap: if that subquery can return null, NOT IN returns no rows at all.

In the SELECT clause

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

This one is correlated — it references c from the outer query, so conceptually it runs once per outer row. On a large table that is expensive, and a LEFT JOIN with GROUP BY is usually faster.

When correlated is fine

EXISTS short-circuits on the first match, so a correlated EXISTS does not scan the whole inner table:

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

That is efficient, and unlike a join it cannot multiply rows.

Key points

An uncorrelated subquery runs once; a correlated one runs per outer row
A correlated subquery in SELECT is often better written as a join
EXISTS short-circuits, so it stays cheap even when correlated

Check yourself