Correlated Subqueries

Overview

A correlated subquery references a column from the outer query, so it is re-evaluated for every row the outer query considers. This is the key difference from a regular (non-correlated) subquery, which runs once and produces a fixed result. The most common pattern is EXISTS: SELECT c.name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid' ) ORDER BY c.name; For each customer row, the inner query checks: 'does at least one paid order exist for THIS customer?' The reference to c.id makes it correlated. Result: Alice, Bob, Carlos, Emma, Farah. Correlated scalar subqueries also work — returning one value per outer row: SELECT c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count FROM customers c ORDER BY c.name; For each customer, the inner query counts THAT customer's orders. Greg and Hana get 0. Mental model: a regular subquery is a constant — computed once, plugged in. A correlated subquery is a function — called once per outer row with that row's values as arguments.

Correlated subqueries express per-row logic that is awkward or impossible with plain JOINs or non-correlated subqueries. 'Find employees earning more than their department average' or 'find customers who have at least one paid order' map naturally to correlated patterns. EXISTS in particular is the idiomatic way to test for the presence or absence of related rows.

Where used: existence checks (does this user have at least one X?), per-row aggregates in SELECT without GROUP BY, row-by-row comparisons against group-level statistics

Why learn this

• Express 'for each row, check or compute something against related rows' — a pattern that appears in nearly every analytics dashboard
• Use EXISTS / NOT EXISTS for clean, index-friendly existence checks instead of awkward LEFT JOIN + IS NULL or IN subqueries

Query walkthrough

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'paid'
)
ORDER BY c.name;

Focus: For each customer row, watch the correlated subquery check whether any paid order exists for THAT customer. The reference to c.id is what makes it correlated.

Common mistakes

• Forgetting the correlation — writing a non-correlated subquery by accident: If the inner WHERE does not reference the outer table, the subquery runs once and returns the same result for every outer row. Always check that the inner query references an outer alias (e.g. WHERE o.customer_id = c.id).
• Using SELECT * inside EXISTS: EXISTS only checks whether at least one row is returned — the actual columns are irrelevant. SELECT 1 or SELECT * both work, but SELECT 1 makes the intent clear and avoids confusion.
• Performance surprise: correlated subqueries run per-row: Because the inner query executes for each outer row, performance can degrade on large tables. Modern Postgres often rewrites them as joins internally, but if a query is slow, consider rewriting as an explicit JOIN or CTE.
• The NOT IN with NULLs trap: If a subquery returns any NULL values, `NOT IN` will evaluate to UNKNOWN for every row, dropping all results. `NOT EXISTS` safely checks for the absence of rows without failing on NULLs, making it generally safer.

Glossary

idiomatic

The most natural, standard, and widely accepted way of doing something in a specific programming language.

degrade

To drop in quality or performance, such as a query becoming slower as the amount of data increases.

alias

A temporary alternative name given to a table to make queries shorter and easier to write.

Recall questions

• What makes a subquery 'correlated'?
• What does EXISTS return?
• How does NOT EXISTS differ from NOT IN when the inner query can return NULLs?

Understanding checks

Which customer names appear?

Greg, Hana

NOT EXISTS returns TRUE when the inner query finds zero rows. Greg (id=7) and Hana (id=8) have no rows in orders, so for them the inner query is empty and NOT EXISTS is TRUE.

Which employees appear and why?

Tom (150000), Wendy (130000)

For each employee, the inner query computes the average salary of THAT employee's department. Executive avg = 200000 — Sara equals it (200000 > 200000 is false). Engineering avg = (150000+120000+110000)/3 = 126666.67 — only Tom (150000) exceeds it. Sales avg = (130000+95000+90000)/3 = 105000 — only Wendy (130000) exceeds it. So only Tom and Wendy appear.

Practice tasks

Flip EXISTS to NOT EXISTS

This query finds customers who have at least one paid order. Modify it to find customers who do NOT have any paid orders. Keep the ORDER BY.

Challenge

Employees earning above their department average

Write a query that returns the name and salary of every employee whose salary is strictly above the average salary in their own department. Order by name.

Continue learning

Previous: Scalar & IN Subqueries

Return to SQL Roadmap