A query inside a query. The inner one runs first and hands its answer to the outer one — except in the correlated case, where it runs again for every single row. Watch that happen.
Four shapes, one idea — and one of them costs a great deal more than the others.
You cannot write WHERE salary > AVG(salary) — an aggregate over the whole table is not something a row-by-row filter can evaluate. What you can do is compute the average in its own query and use the result: that inner query is a subquery.
Everything else follows from what the subquery returns: one value, one column, or a whole table. And from one further question — whether it depends on the outer row or not.
Compare the two comparisons in the lab. The scalar version asks "is this person paid more than the company average?" — one number, computed once, tested against all eight rows. The correlated version asks "is this person paid more than their own department's average?" — a different number per row, so the inner query runs eight times.
The answers differ, and the difference is the point: one employee sits below the company average and above their department's. No single precomputed number could have produced that answer.
Note the "conceptually" above. The optimiser is free to rewrite a correlated subquery into a join or a grouped aggregate, and usually does. But it is not obliged to, and when it cannot, you get one inner execution per outer row — the classic accidentally-quadratic query.
EXISTS takes a correlated subquery and asks only whether it produced any row at all, so the engine can stop at the first match instead of building the full result. For "does this customer have any order?" it is both clearer and cheaper than IN.
It also sidesteps the NULL trap: NOT EXISTS behaves the way people expect NOT IN to behave.
Most subqueries can be written as a join, and joins are often faster because the planner has more freedom with them. Prefer a subquery when it says what you mean more plainly — a membership test reads better as IN or EXISTS than as a join you then have to de-duplicate.
Once a derived table is more than a couple of lines, or you need it twice, lift it into a CTE. Same execution, a name, and no nesting to read inside-out.
A subquery is just a query whose result another query consumes, and its shape is decided by what it returns: one value (scalar), one column (IN), or a table (derived, which must be aliased). An uncorrelated subquery runs once, before the outer query. A correlated one names a column from the outer row, so conceptually it runs once per row — which is both why it can answer per-group questions no single precomputed value could, and why it is the first thing to look at when a query is unexpectedly slow. Reach for EXISTS when you only need to know whether a match exists, and lift anything long or reused out into a CTE.
About to run: Start with the scalar case. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
Without scrolling back — what is the one-line takeaway from this module?
A subquery is just a query whose result another query consumes, and its shape is decided by what it returns: one value (scalar), one column (IN), or a table (derived, which must be aliased). An uncorrelated subquery runs once, before the outer query.
What does this module say about “Quick Context”?
You cannot write WHERE salary > AVG (salary) — an aggregate over the whole table is not something a row-by-row filter can evaluate. What you can do is compute the average in its own query and use the result: that inner query is a subquery.
What does this module say about “The four shapes”?
Compare the two comparisons in the lab. The scalar version asks "is this person paid more than the company average?" — one number, computed once, tested against all eight rows. The correlated version asks "is this person paid more than their own department's average?" — a different number per row, so the inner query runs eight times.
A query inside a query. The inner one runs first and hands its answer to the outer one — except in the correlated case, where it runs again for every single row. Watch that happen.