Subqueries: Scalar, IN, and Correlated

Three shapes of subquery

A subquery is a SELECT nested inside another query. You use one whenever a filter or a computed value depends on another query's result: "orders above the overall average," "customers who have ever ordered," "orders bigger than that customer's own average." There are three shapes, and knowing which is which is a common interview probe.

1. Scalar subquery returns exactly one row, one column; usable anywhere a single value is:

SELECT order_id, total_cents
FROM orders
WHERE total_cents > (SELECT AVG(total_cents) FROM orders);

The inner query yields one number (the overall average); the outer query compares each order to it.

2. IN (or NOT IN) subquery returns one column, many rows; tests set membership:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);   -- customers who have ordered

3. Correlated subquery references the outer row, so it re-runs per outer row:

SELECT o.order_id, o.customer_id, o.total_cents
FROM orders AS o
WHERE o.total_cents > (
  SELECT AVG(o2.total_cents)
  FROM orders AS o2
  WHERE o2.customer_id = o.customer_id   -- ← the correlation: depends on the outer o
);

For each order, the inner query computes that order's customer's average: "orders above their own customer's average."

Anatomy (spot the correlation):

non-correlated: inner query is self-contained, runs ONCE
correlated:     inner query references an outer alias (o), re-evaluated per outer row

EXISTS is the correlated cousin of IN: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id), true if at least one matching row exists. It's the NULL-safe way to write a semi-join.

Performance note. A correlated subquery conceptually re-runs per outer row, which can be slow on large tables. Very often the same result is expressible as a join or a window function (Level 4), which the optimizer executes in one pass. Reach for the correlated form for clarity, but know that "above their own group's average" is a textbook case a window function does faster.

Keep it readable / common pitfall: a scalar subquery that accidentally returns more than one row is not caught by SQLite, the engine this course runs. It silently uses whichever row the subquery produces first, so the mistake surfaces as quietly wrong data rather than an error. (Extra columns are caught: SELECT (SELECT order_id, total_cents FROM orders) raises sub-select returns 2 columns - expected 1. Extra rows are not.) And remember the NOT IN + NULL trap from Level 1: if the subquery can emit a NULL, prefer NOT EXISTS.

In the warehouse this differs. Real warehouses do reject a multi-row scalar subquery: Postgres raises more than one row returned by a subquery used as an expression, and Snowflake and BigQuery raise their own equivalent. SQLite never checks, so a query that fails loudly in production returns silently wrong rows here. Make the subquery provably single-row yourself with an aggregate (AVG, MAX) or a LIMIT 1 instead of trusting the engine to catch it.

Recap: Subqueries come in three shapes: scalar (one value), IN (a column of values), and correlated (re-runs per outer row referencing it); correlated logic is clear but often beaten on speed by a join or a window function.