SQL-01-011-P · Primer — Subqueries
The idea in one sentence
A subquery is a SELECT tucked inside another query — usually in parentheses — that runs first and hands its answer to the outer query, so you can filter or compute against a value the database works out on the fly (like "the average salary" or "the list of busy departments") instead of a number you had to look up and type in by hand.
Why it exists
Sooner or later you write a question that depends on the answer to another question. "Show me everyone paid above the company average" secretly contains a second query — what is the average? — buried inside the first. You could run that query yourself, read off 78.33, and paste it into a WHERE salary > 78.33. But now your query is a lie waiting to happen: the moment someone gets a raise, the real average shifts and your hard-coded number is wrong, silently.
Subqueries exist so the database computes that inner answer for you, every time the query runs, always fresh. You nest the "what is the average?" query right where the number should go, and SQL resolves it before it evaluates the outer filter. This lets a single statement answer a compound question — filter these rows against a value derived from the data itself — without round-trips, temp tables, or copy-pasted magic numbers. It is the difference between asking the database one complete question and babysitting it through several.
How it works
Read a subquery from the inside out: the inner SELECT runs, produces a value, and the outer query uses that value as if you had typed it. The three shapes you'll meet map to what the inner query returns. A scalar subquery returns exactly one value — one row, one column — so it can stand anywhere a single number belongs, like the right side of salary > (...). A WHERE ... IN subquery returns a whole column of values — a list — so dept IN (...) keeps rows whose dept appears anywhere in that list. A correlated subquery is the twist: it mentions a column from the outer row, so it can't run once up front — it re-runs for each outer row, re-computing against that row's own group. The example below shows the most powerful of the three, a correlated subquery that compares each person to the average of their own department:
CREATE TABLE employees (id INTEGER, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO employees VALUES
(1,'Ana','Eng',90), (2,'Ben','Eng',70), (3,'Cleo','Eng',120),
(4,'Dara','Sales',60), (5,'Eli','Sales',80), (6,'Fay','Ops',50);
SELECT name, dept, salary
FROM employees AS e
WHERE salary > (SELECT AVG(salary) -- inner query...
FROM employees
WHERE dept = e.dept) -- ...references outer row e
ORDER BY dept, name;
-- name dept salary
-- ---- ----- ------
-- Cleo Eng 120 Eng avg = 93.33 -> 120 > 93.33 kept
-- Eli Sales 80 Sales avg = 70 -> 80 > 70 kept
-- (Ana 90 is dropped: above the COMPANY avg 78.33, but below her Eng dept avg 93.33)For each employee e, the inner query recomputes the average for that person's department and compares. Ana out-earns the company average yet still falls below her own team's average — which is exactly the nuance a correlated subquery captures and a plain scalar one would miss.
The one gotcha
A scalar subquery must return exactly one value — if it returns two or more rows, the query blows up (or, worse, quietly returns nonsense). The trap looks innocent: WHERE salary > (SELECT salary FROM employees WHERE dept = 'Eng'). You meant "greater than the Eng salary," but Eng has three salaries (90, 70, 120), so the subquery returns three rows into a spot that allows one. PostgreSQL rejects it outright with "more than one row returned by a subquery used as an expression," which is the friendly outcome — you get a loud error and fix it. SQLite is the dangerous one: it silently keeps just the first row it happens to find and compares against that, so your query runs, returns results, and is wrong with no warning at all. The fix is to be honest about how many values you expect. If you want one value, collapse the rows with an aggregate — (SELECT AVG(salary) ...) or MAX, MIN, COUNT — so the subquery is guaranteed scalar. If you genuinely want to test against a list of values, don't use > or =; use IN (or NOT IN), which is built to take a whole column. Match the operator to the shape: one value for =/>, a column of values for IN.
See it — how the three subquery shapes resolve
flowchart TB
Q["Outer query<br/>filters employees"] --> K{"What does the<br/>inner SELECT return?"}
K -->|"one value"| S["Scalar subquery<br/>salary > (AVG ...)<br/>runs ONCE"]
K -->|"a list"| I["IN subquery<br/>dept IN (busy depts)<br/>runs ONCE"]
K -->|"needs outer row"| C["Correlated subquery<br/>WHERE dept = e.dept<br/>RE-RUNS per row"]
S --> R["Rows that pass<br/>the condition"]
I --> R
C --> R
The one-line takeaway
Nest a SELECT inside another to filter or compute against a value the database derives for you: one value feeds =/> (scalar), a list feeds IN, and a subquery that references the outer row (correlated) re-runs per row.
Where you'll meet it
You'll meet subqueries the first time a question folds a second question inside it — "who earns above average?", "which products never sold?", "customers who ordered last month but not this one." They're everywhere in reporting and in application code that filters one table by facts living in another. Interviewers love them precisely because they separate people who memorize JOIN syntax from people who can think in nested logic: expect "find employees paid more than their department's average" (a correlated subquery), "list customers who have never placed an order" (WHERE id NOT IN (SELECT customer_id FROM orders) — and a great follow-up about how NOT IN behaves when the inner list contains NULL), and "rewrite this subquery as a JOIN." The strong answer knows all three shapes, can say why a correlated subquery re-runs per row while a scalar one runs once, and knows when a subquery is clearer than a join and when it isn't. Get comfortable reading them inside-out, and compound questions stop being scary.
Named concepts: scalar subquery, subquery in WHERE with IN, correlated subquery