Subquery
A query nested inside another query. Three common positions: in the FROM clause (derived table), in the SELECT list (scalar subquery, must return at most one value), and in predicates with IN / NOT IN / = (SELECT ...). A subquery that references the outer row is correlated (covered separately).
Syntax #
sql
-- IN predicate
SELECT *
FROM a
WHERE key IN (SELECT key FROM b);
-- Derived table
SELECT t.*
FROM (SELECT key, SUM(x) AS s FROM b GROUP BY key) AS t;Example #
Loading SQL editor...
Warning
NOT IN plus NULL = zero rows. If the subquery returns any NULL in its result set, every NOT IN comparison becomes unknown, and the outer query silently returns nothing. This is one of SQL's most common "empty result, no error" traps. For exclusion against a subquery, prefer NOT EXISTS or LEFT JOIN ... WHERE right.key IS NULL. Both are NULL-safe and usually as fast or faster.