A subquery is a query nested inside another — used to feed a value, a list, or a whole result set into the outer query.
A subquery that returns exactly one value, usable anywhere a single value is expected.
WHERE column = (SELECT ... );SELECT name FROM Employee
WHERE salary = (SELECT MAX(salary) FROM Employee);Common Mistake
Using = when the subquery could return more than one row, which causes an error.
A subquery that returns a list of values, paired with IN, ANY, or ALL.
WHERE column IN (SELECT ... );SELECT name FROM Employee
WHERE department_id IN (
SELECT id FROM Department WHERE location = 'Kolkata'
);Common Mistake
Using = instead of IN when the subquery can return multiple rows.
A subquery that references a column from the outer query, re-running once per outer row.
WHERE EXISTS (SELECT 1 FROM ... WHERE outer.col = inner.col);SELECT name FROM Employee e
WHERE salary > (
SELECT AVG(salary) FROM Employee
WHERE department = e.department
);Common Mistake
Using a correlated subquery over a huge table without an index — it can be slow, since it re-runs per outer row.
Treats a subquery as a temporary table that the outer query can select from.
SELECT * FROM (SELECT ...) AS alias;SELECT department, avg_salary
FROM (
SELECT department, AVG(salary) AS avg_salary
FROM Employee GROUP BY department
) AS dept_avg
WHERE avg_salary > 60000;Common Mistake
Forgetting the subquery needs an alias — most engines require one.
| Kind | Returns | Typically used with |
|---|---|---|
| Single-row | Exactly one value | =, >, < |
| Multi-row | A list of values | IN, ANY, ALL |
| Correlated | Re-evaluated per outer row | EXISTS, comparisons referencing the outer query |
| In FROM | A whole result set | Treated as a temporary table, needs an alias |
Subqueries nest one query inside a clause of another. The next lesson covers a cleaner way to do something similar — Common Table Expressions, which name a subquery so it reads top-to-bottom instead of nested.