Subqueries — SQL Tutorial with Examples

What You'll Learn

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.

Syntax

-- WHERE subquery
SELECT * FROM table
WHERE col > (SELECT AVG(col) FROM table);

-- FROM subquery (derived table)
SELECT * FROM (SELECT ...) AS alias;

Example

-- 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
);

Beyond the Basics

Data in play — reference tables for this topic

-- employees (from Topic 1) — the table every subquery below scans
-- 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 classic 'above average' — why it can't be a WHERE constant

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 6800

Any time a filter compares rows to an aggregate of the same rows, a subquery is the mechanism — WHERE can only see per-row values.

Correlated = one execution per outer row

The 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)

Correlated queries are correct-but-slow at best — and silently wrong when NULLs join the comparison. Topics 17-18 rewrite this exact query in one pass.

NOT IN vs NOT EXISTS — the trap Topic 7 promised

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-proof

NOT EXISTS asks 'is there a match?' per row — NULL can never answer that question with a false negative. Default to NOT EXISTS.

Common Mistakes

Correlated 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.

Interview Tips

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.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

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…?

Practice

Return a single value: the second-highest salary in the employees table.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Subquery vs JOIN — which is faster?

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.

What is a scalar subquery?

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.