Subqueries — correlated vs uncorrelated: Worked examples

This is the "cover every case" workshop for the parent topic. Before we solve anything, we lay out every kind of situation a subquery question can throw at you. Then each worked example is stamped with the exact cell it covers, so by the end no scenario is left dark.

If a word here feels unexplained, it was built in the parent note — but we re-anchor the key ones as we go.

The scenario matrix

Think of a subquery question as a point on a grid. Two big axes decide almost everything:

Axis 1 — does the inner query borrow a column from the outer row? No = uncorrelated (runs once). Yes = correlated (runs conceptually once per outer row).
Axis 2 — what SHAPE does the inner query return? A single number (scalar), a single column of many values (list, tested with IN/ANY/ALL — quantifier operators we fully define in Cell K), or just a yes/no existence flag (boolean via EXISTS).

On top of those two axes sit the nasty edge cases that break naive answers: empty results, NULL values, and degenerate inputs (a table with zero rows, a group of size one). Here is the full board.

Cell	Correlation	Inner shape	The trap it tests
A	Uncorrelated	Scalar	Global constant threshold
B	Uncorrelated	List (`IN`)	Membership test
C	Uncorrelated	List with a `NULL` inside	`NOT IN` silently returns nothing
D	Correlated	Scalar aggregate	Per-row threshold
E	Correlated	Boolean (`EXISTS`)	Short-circuit existence
F	Correlated	Boolean (`NOT EXISTS`)	"Has none" / anti-join
G	Degenerate	Empty inner result	`AVG` of nothing = `NULL`, comparison collapses
H	Degenerate	Group of size 1	Row is its own average → strict `>` fails
I	Real-world word problem	Correlated scalar	Reading English into SQL
J	Exam twist	Correlated	Rewrite as a [[Joins — inner outer cross
K	Uncorrelated	List with `> ALL` / `> ANY`	Quantified comparison over a whole list

The eleven worked examples below (A through K) hit every cell of this matrix.

Before the examples, meet the data pictorially — the red "correlation" arrows in the figure are the whole point of this chapter, so keep the picture in your head.

Definition Our sample data

employee(id, name, salary, dept_id)

id	name	salary	dept_id
1	Ana	40000	10
2	Bo	60000	10
3	Cy	55000	20
4	Di	55000	20
5	Ed	90000	30

customer(id, name) = {1: Kim, 2: Lee, 3: Mo} orders(id, customer_id) = {(101, 1), (102, 1), (103, 3)} (Customer 2 = Lee placed no order.)

Figure s01 — what it draws (readable without the image): on the left, three blue boxes stack the customers Kim(id 1), Lee(id 2), Mo(id 3). On the right, three green boxes stack the orders 101, 102, 103, each tagged with the customer it belongs to (101→cust 1, 102→cust 1, 103→cust 3). A red arrow is drawn from each order back to its owning customer whenever orders.customer_id = customer.id. So Kim receives two red arrows (orders 101 and 102), Mo receives one (order 103), and Lee receives none. That missing arrow on Lee is the single most important thing in the picture: every correlated example (D, E, F, J) walks along these arrows, and the anti-join (Cell F) is literally "find the customer with no arrow" — Lee.

Figure — Subqueries — correlated vs uncorrelated

Cell A — Uncorrelated scalar (global threshold)

Worked example Employees who earn above the company average

SELECT name, salary
FROM   employee
WHERE  salary > (SELECT AVG(salary) FROM employee);

Forecast: guess how many names come back before reading on.

Picture a single horizontal line drawn across all five salaries at the average — one line for everyone (this is the left panel of figure s02, described just below). That "one shared line" is what makes this uncorrelated.

Compute the inner query once. AVG(salary) over {40000, 60000, 55000, 55000, 90000}. In plain arithmetic: 300000 ÷ 5 = 60000. Why this step? The inner SELECT mentions no outer column, so it is a fixed constant — a single number the outer query reuses. That is the whole meaning of uncorrelated: unplugged from the outer row. $AVG = \frac{40000 + 60000 + 55000 + 55000 + 90000}{5} = \frac{300000}{5} = 60000$
Substitute. The query becomes WHERE salary > 60000. Why this step? Once the constant exists, the filter is an ordinary per-row comparison.
Filter. Salaries strictly above 60000: only Ed (90000). Bo is exactly 60000, so > excludes him. Why this step? Strict > vs >= is where most people miscount — Bo sits on the boundary.

Verify: Count of employees strictly above 60000 = 1 (Ed). Sanity: the average must lie between min (40000) and max (90000) — 60000 does. ✓

Figure s02 — what it draws (readable without the image): two side-by-side bar charts of the five salaries (Ana 40k, Bo 60k, Cy 55k, Di 55k, Ed 90k), bars coloured by department (dept 10 blue, dept 20 yellow, dept 30 green). The left panel overlays one red dashed horizontal line at 60000 — the single global average of Cell A; only Ed's bar pokes above it. The right panel instead draws one short red line per department at that department's own average (dept 10 at 50000, dept 20 at 55000, dept 30 at 90000) — the per-group thresholds of Cell D; here only Bo's bar clears its own department's line. Left = "one shared line" (uncorrelated), right = "one line per group" (correlated).

Cell B — Uncorrelated list with `IN`

Worked example Customers who placed at least one order

SELECT name
FROM   customer
WHERE  id IN (SELECT customer_id FROM orders);

Forecast: which of Kim, Lee, Mo appear?

Build the list once. Inner query returns the column customer_id from orders: {1, 1, 3}, i.e. the distinct set {1, 3}. Why this step? No outer column is referenced → uncorrelated → the list is materialised a single time.
Membership-test each outer row. id IN {1,3}? Kim(1)=yes, Lee(2)=no, Mo(3)=yes. Why this step? IN is exactly "is this value one of the listed values?" — geometrically, "does this customer own at least one red arrow in figure s01?"

Verify: Result = {Kim, Mo}, size 2. Cross-check: customer 2 (Lee) is the only one with no order, so exactly the other 2 qualify. ✓

Cell C — The `NOT IN` + `NULL` trap

Worked example Customers who placed NO order (the buggy way)

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

Suppose orders also contained one bad row with customer_id = NULL. So the list is {1, 3, NULL}. Forecast: you'd expect {Lee}. Guess what actually comes back.

Expand NOT IN into its logical form. id NOT IN (1, 3, NULL) means id <> 1 AND id <> 3 AND id <> NULL. Why this step? NOT IN is defined as an and of "not equal to each element." This is where the poison enters.
Evaluate the id <> NULL term. In SQL, any comparison with NULL yields UNKNOWN, not TRUE. (See NULL handling in SQL.) Why this step? NULL means "unknown value"; the DB cannot claim 2 <> unknown is true.
Combine. TRUE AND TRUE AND UNKNOWN = UNKNOWN. A WHERE keeps a row only when the condition is TRUE, never when UNKNOWN. Why this step? Three-valued logic: UNKNOWN is filtered out just like FALSE.
Result: zero rows. Even Lee, who genuinely placed no order, is dropped. Why this step? This is the classic silent bug: no error, just wrong (empty) output.

Fix: use the correlated NOT EXISTS (Cell F) which is NULL-safe. Verify: Buggy NOT IN result count = 0. Correct answer should be 1 (Lee). The mismatch is the whole lesson. ✓

Cell D — Correlated scalar (per-row threshold)

Worked example Employees above THEIR OWN department's average

SELECT e.name, e.salary
FROM   employee e
WHERE  e.salary > (SELECT AVG(e2.salary)
                   FROM   employee e2
                   WHERE  e2.dept_id = e.dept_id);

Forecast: which names survive? (dept 10 = {40k, 60k}, dept 20 = {55k, 55k}, dept 30 = {90k}.)

Now picture the right panel of figure s02 (described above): instead of one shared line, each department gets its own average line at a different height. That "one line per group" is the visual signature of correlation.

Spot the correlation. The inner WHERE e2.dept_id = e.dept_id borrows e.dept_id from the outer row. Why this step? That borrowed column is what forces re-evaluation per row — the threshold is personal, not global.
Re-run per department.
- Dept 10 avg = (40000+60000)/2 = 50000. Ana 40000 > 50000? No. Bo 60000 > 50000? Yes.
- Dept 20 avg = (55000+55000)/2 = 55000. Cy 55000 > 55000? No. Di 55000 > 55000? No.
- Dept 30 avg = 90000. Ed 90000 > 90000? No. Why this step? Each row is compared against its own group's number, the essence of a correlated aggregate.
Collect winners. Only Bo. Why this step? Strict > again excludes the two 55000s and lone Ed who equal their group average.

Verify: Result = {Bo}, count 1. Contrast Cell A (global): there Ed won; here Ed loses because he is his department. Same person, different question. ✓

Cell E — Correlated boolean `EXISTS`

Worked example Customers with at least one order (correlated version)

SELECT c.name
FROM   customer c
WHERE  EXISTS (SELECT 1
               FROM   orders o
               WHERE  o.customer_id = c.id);

Forecast: same answer as Cell B or different?

Read EXISTS as a yes/no. For each customer, the inner query asks "is there any order row where o.customer_id equals this customer's id?" Why this step? EXISTS doesn't care how many rows match — it returns TRUE the instant it finds one (short-circuit). The SELECT 1 is a throwaway; only the row's existence matters. Visually: "does this customer have any red arrow leaving it in figure s01?"
Loop the outer rows. Kim(1): order 101 matches → TRUE. Lee(2): no matching order → FALSE. Mo(3): order 103 matches → TRUE. Why this step? c.id changes each pass — the correlation in action, walking one red arrow at a time.

Verify: Result = {Kim, Mo}, count 2 — identical to Cell B's IN. Same set, but EXISTS short-circuits and survives NULLs. (See EXISTS vs IN vs ANY.) ✓

Cell F — Correlated `NOT EXISTS` (the NULL-safe anti-join)

Worked example Customers who placed NO order (the correct way)

SELECT c.name
FROM   customer c
WHERE  NOT EXISTS (SELECT 1
                   FROM   orders o
                   WHERE  o.customer_id = c.id);

Forecast: does the NULL order row (from Cell C) break this?

Geometrically this is "find the customer with no red arrow" in figure s01 — Lee.

Negate the existence flag per row. Keep customers for whom no matching order exists. Why this step? This is the intended meaning of "placed no order" — the anti-join, the hunt for the missing arrow.
Why the NULL doesn't poison it. The inner WHERE o.customer_id = c.id simply fails to match the NULL row (NULL = 1 is UNKNOWN, treated as no-match), so that garbage row is just ignored — it never turns a real answer into UNKNOWN the way NOT IN did. Why this step? EXISTS asks "did any row survive the WHERE?" — UNKNOWN rows don't survive, and that's harmless here. Contrast Cell C where UNKNOWN infected the outer condition.
Evaluate. Kim: has orders → NOT EXISTS = FALSE. Lee: no orders → TRUE. Mo: has orders → FALSE.

Verify: Result = {Lee}, count 1 — the correct answer that Cell C's NOT IN got wrong (it returned 0). This is exactly why we prefer NOT EXISTS. ✓

Cell G — Degenerate: empty inner result

Worked example Salary compare against an empty department

SELECT e.name
FROM   employee e
WHERE  e.salary > (SELECT AVG(e2.salary)
                   FROM   employee e2
                   WHERE  e2.dept_id = 99);   -- no employee is in dept 99

Forecast: how many rows? Zero? All? Error?

Evaluate the inner aggregate on an empty set. No rows have dept_id = 99, so AVG(...) over zero rows returns NULL, not 0 and not an error. Why this step? AVG, SUM, MAX, MIN all return NULL for an empty input (only COUNT returns 0). Knowing this prevents the "why is it empty?!" panic.
Compare against NULL. e.salary > NULL evaluates to UNKNOWN for every row. Why this step? Any arithmetic comparison with NULL is UNKNOWN (same rule as Cell C).
WHERE drops all UNKNOWN rows. Result = 0 rows. Why this step? UNKNOWN is not TRUE, so nothing is kept — a silent empty result, no error raised.

Verify: Result count = 0. Sanity: AVG of empty = NULL is the trigger, and NULL comparison → all-UNKNOWN → empty. ✓

Cell H — Degenerate: group of size one

Cell I — Real-world word problem

Worked example "Show me every product priced above the average price of products in its own category, but only in categories that actually have more than one product."

Model tables: product(id, category_id, price). Use the same employee numbers as a stand-in by mapping category→dept, price→salary. Forecast: which of the "why this step" phrases from Cell D reappear, and what new clause does "more than one product" add?

Translate "its own category's average" → correlated scalar subquery: price > (SELECT AVG(p2.price) FROM product p2 WHERE p2.category_id = p.category_id). Why this step? The phrase "its own X" means the threshold is personal to each row's category, not a global constant — and "personal per row" is precisely the definition of correlation (Cell D). So the plain-English "its own" is the fingerprint that tells us to reference the outer column p.category_id inside the subquery.
Translate "categories with more than one product" → add a group-size guard: append AND (SELECT COUNT(*) FROM product p3 WHERE p3.category_id = p.category_id) > 1. Why this step? This deliberately excludes the Cell H degenerate case (solo categories), where a lone product is its own average and could never beat it under >. Guarding on COUNT(*) > 1 keeps the result meaningful.
Apply to our data (dept 10 = 2 rows, dept 20 = 2 rows, dept 30 = 1 row): dept 30 is filtered out by the COUNT guard, within dept 10 only Bo beats 50000, and in dept 20 nobody beats 55000. Why this step? Combining Cells D + H + a COUNT subquery shows how a real English request stacks several conditions into one query.

Verify: Final winners = {Bo}, count 1. The COUNT guard removed Ed's category entirely; strict > handled the rest. ✓

Cell J — Exam twist: rewrite the correlated subquery as a JOIN

Worked example Prove the Cell E

EXISTS query equals a JOIN

-- Correlated EXISTS version (Cell E)
SELECT c.name
FROM   customer c
WHERE  EXISTS (SELECT 1 FROM orders o
               WHERE o.customer_id = c.id);
 
-- Rewritten as an inner join + DISTINCT
SELECT DISTINCT c.name
FROM   customer c
JOIN   orders o ON o.customer_id = c.id;

Forecast: do they return the same set? Why the DISTINCT?

See why a plain join over-counts. A join emits one output row per matching red arrow in figure s01. Kim has two arrows (orders 101, 102) → Kim appears twice. Why this step? EXISTS only asks "any arrow?" (so at most one row per customer), whereas a raw join copies the customer once per arrow. We must collapse those duplicates to match EXISTS semantics — that collapse is what turns a join into a semi-join.
Add DISTINCT to collapse duplicates. SELECT DISTINCT c.name folds Kim's two rows back into one. Why this step? DISTINCT removes exact duplicate output rows, restoring the one-row-per-customer shape that EXISTS guarantees for free.
Compare the sets. Join+DISTINCT gives {Kim, Mo}; EXISTS gives {Kim, Mo}. Equal. Why this step? This equality is the rewrite the optimizer performs internally — it proves the "correlated = always slow" myth false, since the correlated form and the fast join form are literally the same result.
Know when they'd differ. Drop the DISTINCT and the plain join returns 3 rows (Kim, Kim, Mo) — a count mismatch against EXISTS's 2. That missing DISTINCT is the classic exam trap. Why this step? Recognising the duplicate-count difference is how you catch a wrong "equivalent" rewrite under exam pressure.

Verify: EXISTS count = 2; join+DISTINCT count = 2; plain join (no DISTINCT) count = 3. Equality holds only with DISTINCT. ✓

Cell K — Uncorrelated list with `> ALL` / `> ANY`

Worked example Employees who out-earn EVERY employee in department 20

SELECT name, salary
FROM   employee
WHERE  salary > ALL (SELECT salary FROM employee WHERE dept_id = 20);

Forecast: dept 20 salaries are {55000, 55000}. Who beats all of them?

First, the definitions promised in the matrix. > ALL(list) and > ANY(list) are a third list-shape, sitting between IN (which only ever tests equality) and a plain scalar. They answer a quantified comparison:

x > ALL(list) is TRUE when x is greater than every single element — equivalently, x > MAX(list). Think "beat the biggest."
x > ANY(list) is TRUE when x is greater than at least one element — equivalently, x > MIN(list). Think "beat the smallest."

Materialise the list once. Inner query returns dept-20 salaries: {55000, 55000}. No outer column → uncorrelated. Why this step? Same "run once" logic as Cells A and B; only the operator differs.
Apply the > ALL definition. salary > ALL {55000, 55000} means salary > 55000 AND salary > 55000, i.e. salary > MAX = 55000. Why this step? Reducing > ALL to a single > MAX comparison is the key trick that makes the quantifier easy to evaluate by hand.
Filter. Who is > 55000? Bo (60000) yes, Ed (90000) yes; Ana (40000) no, and Cy/Di (55000) no (not strictly greater). Why this step? Strict > on the boundary excludes the 55000s themselves.
Contrast > ANY. Had we written > ANY {55000, 55000}, it means > MIN = > 55000 — here the same threshold, because dept 20 has one distinct value. In general > ANY is a looser filter than > ALL, since MIN ≤ MAX. Why this step? Showing both quantifiers side by side closes the whole list-comparison case: ALL→max, ANY→min.

Verify: > ALL result = {Bo, Ed}, count 2 (equivalently > MAX = > 55000). And > ANY here = > MIN = > 55000 = same 2 rows, because dept 20 has one distinct value. ✓

Recall Quick self-test on the matrix

Which cell does "employees earning more than the company average" hit? ::: Cell A — uncorrelated scalar, global constant threshold. Which two cells return {Kim, Mo}? ::: Cell B (IN) and Cell E (EXISTS) — same set, different mechanism. Why did NOT IN (Cell C) return zero customers instead of {Lee}? ::: A NULL in the list makes id <> NULL UNKNOWN, so the whole AND-chain is UNKNOWN and every row is filtered out. What does AVG of an empty group return, and what does comparing to it do? ::: NULL; any comparison to NULL is UNKNOWN, so the WHERE keeps zero rows (Cell G). Why must the JOIN rewrite (Cell J) use DISTINCT? ::: A customer with several orders produces several join rows; DISTINCT collapses them to match the one-row-per-customer semantics of EXISTS. What does > ALL(list) reduce to, and > ANY(list)? ::: > ALL = greater than the MAX of the list; > ANY = greater than the MIN of the list (Cell K).

Connections

Subqueries — correlated vs uncorrelated (index 4.4.7) — the parent this workshop drills into.
Joins — inner outer cross — Cell J's semi-join rewrite.
Aggregate Functions GROUP BY — Cells A, D, G, H lean on AVG/COUNT.
EXISTS vs IN vs ANY — Cells C, E, F, K contrast these operators.
NULL handling in SQL — the three-valued logic behind Cells C and G.
Query Optimizer Execution Plans — why Cell J's rewrite matters for speed.