SQL Topic
Subqueries
A subquery is a SELECT nested inside another query — in FROM (a derived table), in WHERE (scalar or IN comparisons), or in SELECT (a scalar expression). Subqueries are the building block for 'second highest', 'above average', and 'customers who did X'.
When is Subqueries used?
Comparing a value to an aggregate (above average), excluding or including sets (IN / NOT IN), or pre-shaping data before the outer query groups it.
Core syntax
SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee) — a scalar subquery returning one value.Common mistakes
- Using NOT IN against a set that contains NULL (the whole comparison becomes UNKNOWN).
- Returning multiple columns where a scalar is expected.
- Correlating a subquery without meaning to, or with a wrong alias.
Practice Subqueries questions
Work through them in order — each question builds on the last.