Loading…
Loading…
SQL tutorial · Advanced
⏱️ 17 mins read
A subquery is a query nested inside another. Non-correlated subqueries run once independently. Correlated subqueries reference the outer query and run once per outer row — they can be slow on large tables. Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses.
-- WHERE subquery
SELECT * FROM table
WHERE col > (SELECT AVG(col) FROM table);
-- FROM subquery (derived table)
SELECT * FROM (SELECT ...) AS alias;-- Non-correlated: runs once
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees); -- avg = 98800
-- Subquery in FROM (derived table)
SELECT dept, avg_sal
FROM (
SELECT department_id AS dept,
AVG(salary) AS avg_sal
FROM employees GROUP BY department_id
) dept_stats
WHERE avg_sal > 75000;
-- Correlated: runs per outer row (SLOW)
SELECT e.name, e.salary
FROM employees e
WHERE e.salary = (
SELECT MAX(e2.salary) FROM employees e2
WHERE e2.department_id = e.department_id -- references outer e
);| id | name | department_id | salary |
|---|---|---|---|
| 1 | Ada | 1 | 145000 |
| 2 | Grace | 1 | 115000 |
| 3 | Alan | 2 | 92000 |
| 4 | Edsger | NULL | 78000 |
| 5 | Barbara | 3 | 64000 |
The company average is 98800 — but you cannot write WHERE salary > 98800 and be done: the number must be computed from the same rows you're filtering, and it must stay correct as data changes. A subquery lets the engine compute the constant on the fly, once, before the filter runs.
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- AVG = 494000 / 5 = 98800
-- Returns: Ada (145000), Grace (115000) — Alan misses by 6800The third example re-runs its inner query for every employee. Note the fix we made: the inner table needs its own alias (e2), or it would shadow the outer reference. Trace it and you'll also find a NULL surprise — the query can't classify Edsger at all.
SELECT e.name, e.salary
FROM employees e
WHERE e.salary = (
SELECT MAX(e2.salary) FROM employees e2
WHERE e2.department_id = e.department_id
);
-- Ada ✓ (max of dept 1), Alan ✓, Barbara ✓
-- Grace ✗ (145000 ≠ 115000)
-- Edsger ✗✗ — e2.department_id = NULL matches NOTHING,
-- MAX over empty set = NULL, and salary = NULL is UNKNOWN (Topic 7)Topic 7's FAQ warned that NOT IN with a NULL in the subquery returns nothing. Now you can see why it matters in practice: 'customers with no orders' (the anti-join from Topic 15) breaks if any order has a NULL customer_id — one NULL poisons the entire NOT IN chain.
-- Breaks if ANY orders.customer_id is NULL:
SELECT c.name FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);
-- x NOT IN (1, 2, NULL) = x != 1 AND x != 2 AND x != NULL → can never be TRUE
-- Immune:
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Maike — the anti-join, NULL-proofCorrelated subqueries run N times (once per row in the outer query). On a 1M-row table, that's 1M subquery executions. Prefer JOINs or CTEs for performance. Also: NOT IN with a subquery that returns NULLs always produces empty results — use NOT EXISTS instead.
Know when to use EXISTS vs IN. EXISTS stops at the first match (faster for large sets). Correlated subqueries are often rewritable as JOINs — show both approaches and discuss trade-offs.
1. Why can't the 'above average salary' filter just use a constant like 98800?
2. What makes a subquery 'correlated'?
3. The correlated top-earner-per-department query — what happens to Edsger?
4. Why does the inner query need its own alias (e2) in a correlated subquery?
5. Why is NOT IN with a subquery dangerous?
6. A scalar subquery is one that…?
Return a single value: the second-highest salary in the employees table.
⚡ Solve it in the SQL playground →For non-correlated subqueries the optimizer usually produces the same plan as the equivalent JOIN, so write whichever reads better. Correlated subqueries are the dangerous ones — they can degrade to N executions, though modern optimizers sometimes rewrite them as joins anyway. Measure with EXPLAIN (Topic 22), don't guess.
One placed where a single value is expected — for example in the SELECT list: SELECT name, (SELECT MAX(salary) FROM employees) AS top_salary FROM employees. It must return at most one row or the query errors at runtime. Handy, but it runs per output row, so treat it like a correlated subquery.