The idea: a query inside a query, asked once or once per row

A subquery is a SELECT in parentheses inside another statement. The outer query walks its rows; for each row, a test such as IN (…) or EXISTS (…) uses the subquery's answer. The first question to ask about any subquery is: does it use a column of the outer row? If not, its answer is the same for every row, so it is needed only once. If it does, it is a different question for every row.

Left: an uncorrelated subquery, SELECT e.dept_id FROM emp e, runs once and returns 10, 10, 20, NULL; the same list is used for Sales, IT and HR. Right: a correlated subquery with WHERE e.dept_id = d.id runs three times, once with d.id 10 for Sales, once with 20 for IT, once with 30 for HR.
An uncorrelated subquery gives one answer shared by all outer rows; a correlated subquery is asked again with each outer row's value.

The data on the canvas

Two small tables. dept(id, name) holds Sales (10), IT (20) and HR (30); nobody works in HR. emp(id, name, dept_id, salary) holds Ann (10, 5000), Bob (10, 3000), Cid (20, 4000) and Dee, who has dept_id NULL and salary 3500. Dee's NULL is there on purpose: it is what makes subqueries surprising.

The canvas shows what each query means, evaluated the plain way: outer row by outer row, running the subquery when it is needed. MariaDB returns exactly these rows, but its optimizer usually finds a faster plan, such as turning IN into a semi-join or running a subquery once into a temporary table. That side is shown on MariaDB Subqueries and Views: Merge, Materialize, Semi-join.

Uncorrelated vs correlated

In WHERE d.id IN (SELECT e.dept_id FROM emp e) the subquery mentions only emp. It is uncorrelated: it runs once, reads the 4 emp rows and returns the list (10, 10, 20, NULL). Every dept row is then tested against that same list (Demo: IN, the subquery runs once: 1 run, 4 rows read).

In WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.id) the subquery uses d.id, a column of the outer row. It is correlated: logically it runs once per dept row, with d.id = 10, then 20, then 30 (Demo: NOT EXISTS, re-run per department: 3 runs). EXISTS only asks whether a row exists, so it can stop at the first match: 1 row read for Sales, 3 for IT and all 4 for HR, 8 in total. A correlated subquery can also stand in a comparison: "employees paid more than the average of their own department" runs the AVG once per employee (Demo: correlated AVG, Dee gets NULL: 4 runs, 16 rows read, only Ann is kept).

A correlated subquery over a big table, asked once per row, can be slow. MariaDB helps in two ways: it often rewrites the query so it is no longer run per row, and its subquery cache (optimizer_switch flag subquery_cache, on by default) remembers the answer for each outer value. In the AVG demo, Bob has the same dept_id as Ann, so MariaDB would reuse Ann's answer instead of running the subquery again.

IN and three-valued logic

SQL conditions have three values: TRUE, FALSE and UNKNOWN. Any comparison with NULL, such as 30 = NULL, is UNKNOWN. WHERE keeps a row only when its condition is TRUE; FALSE and UNKNOWN both drop it.

x IN (a, b, c) means x = a OR x = b OR x = c. So it is TRUE if one value equals x. If none does, it is FALSE, unless the list holds a NULL: then one part is UNKNOWN and the whole IN is UNKNOWN. For HR, 30 IN (10, 10, 20, NULL) is FALSE, FALSE, FALSE, UNKNOWN, so UNKNOWN. HR is dropped either way, and the result is Sales and IT.

The NOT IN trap, and NOT EXISTS

"Departments with nobody in them" looks like WHERE d.id NOT IN (SELECT e.dept_id FROM emp e). It returns no rows at all (Demo: NOT IN with a NULL returns nothing). NOT IN is NOT (IN), and NOT UNKNOWN is still UNKNOWN. For HR the test is UNKNOWN because of Dee's NULL; for Sales and IT it is FALSE. One NULL anywhere in the subquery's result empties a NOT IN.

The HR row, d.id 30. NOT IN: 30 compared with 10, 10, 20 is not equal each time, but 30 compared with NULL is UNKNOWN, so NOT IN is UNKNOWN and HR is dropped, 0 rows. NOT EXISTS: the subquery with e.dept_id = 30 finds no row, so NOT EXISTS is TRUE and HR is kept, 1 row.
For HR, one NULL makes NOT IN UNKNOWN, so the row is lost; NOT EXISTS only asks whether a row exists and keeps HR.

Two fixes give the expected answer, HR:

  • Filter the NULLs inside: NOT IN (SELECT e.dept_id FROM emp e WHERE e.dept_id IS NOT NULL). Now 30 NOT IN (10, 10, 20) is TRUE (Demo: NOT IN fixed with IS NOT NULL).
  • Use NOT EXISTS with a correlated condition. EXISTS is only ever TRUE or FALSE: a subquery returns a row or it does not. Dee's row does not match e.dept_id = 30 (it is UNKNOWN, so it is not a match), and that is all it does.

A NULL on the outer side has the same effect: if a dept row had id NULL, NULL IN (…) would be UNKNOWN for any non-empty list. Declaring the column NOT NULL removes the trap for good.

ANY, SOME and ALL

A comparison can be put in front of a subquery that returns a column. x > ANY (…) (SOME is the same word) is TRUE if x is bigger than at least one value, so it means "bigger than the smallest". x > ALL (…) is TRUE if x is bigger than every value. With the salaries of dept 10, (5000, 3000), salary > ANY keeps Ann, Cid and Dee, everyone above 3000 (Demo: > ANY, bigger than the smallest).

Two edge cases follow from the definitions. Over an empty set, ANY is FALSE and ALL is TRUE for every row: query 9, salary > ALL of dept 30's salaries, returns all 4 employees. And = ANY is exactly IN, while <> ALL is exactly NOT IN, NULL trap included.

Scalar subqueries

A subquery that returns one row with one column is a scalar subquery. It can go wherever a single value can: in a comparison, as above, or as a column in SELECT. SELECT d.name, (SELECT COUNT(*) FROM emp e WHERE e.dept_id = d.id) AS staff FROM dept d gives Sales 2, IT 1, HR 0 (Demo: scalar subquery in SELECT).

A scalar subquery that finds no row gives NULL. An aggregate always returns one row, but its value over no rows differs: COUNT(*) is 0, while AVG, SUM, MAX are NULL. That is why Dee drops out of the AVG demo: no row has x.dept_id = NULL, her average is NULL, and 3500 > NULL is UNKNOWN. If a scalar subquery returns more than one row, MariaDB stops with error 1242, "Subquery returns more than 1 row".

The equivalent join

Query 1 can be written as a join: SELECT DISTINCT d.name FROM dept d JOIN emp e ON e.dept_id = d.id. The join compares 12 pairs and returns 3 rows, because Sales has two partners, Ann and Bob. A subquery test never repeats an outer row; a join returns one row per partner. Only DISTINCT brings the 3 rows back to the 2 of IN (Demo: IN as a join needs DISTINCT). How joins pair rows is shown on SQL Joins on Real Rows; "NOT EXISTS" can also be written as LEFT JOIN … WHERE e.id IS NULL, which keeps the NULL-padded HR row.

Summary

Query hereSubquery runsResult
d.id IN (SELECT e.dept_id …)1Sales, IT
d.id NOT IN (SELECT e.dept_id …)1no rows (Dee's NULL)
NOT IN (… WHERE e.dept_id IS NOT NULL)1HR
NOT EXISTS (… WHERE e.dept_id = d.id)3HR
DISTINCT … JOIN emp e ON …none, 12 pairs3 rows → Sales, IT
e.salary > (SELECT AVG … = e.dept_id)4Ann
(SELECT COUNT(*) …) AS staff3Sales 2, IT 1, HR 0
e.salary > ANY (dept 10)1Ann, Cid, Dee
e.salary > ALL (dept 30, empty)1all 4

See also SQL Joins on Real Rows for how joins pair rows and pad with NULL, MariaDB Subqueries and Views: Merge, Materialize, Semi-join for how the optimizer really runs IN, NOT IN and derived tables, and SQL SELECT Order for the order of the clauses.

What the page leaves out

Subqueries in FROM (derived tables) and in UPDATE or DELETE, row subqueries such as (a, b) IN (SELECT x, y …), and WITH common table expressions are only named here. The run counts are the logical ones; MariaDB's semi-join strategies, materialization and subquery cache change how often the work is really done, not the rows that come out. MariaDB does not allow LIMIT inside an IN, ANY or ALL subquery (error 1235, "doesn't yet support"). The canvas lists rows in the order its loop produces them; without ORDER BY MariaDB promises no order.

On the mind map

Open the Databases & Storage mind mapDatabases& StorageSchema and storage: how rows land on diskSchema and storageIndexes: find rows without a full scanIndexesRunning a query: parse, plan, executeRunning a queryCost-based optimizer: estimate rows, pick cheapest planCost-based optimizerQuery rewrites: same answer, simpler queryQuery rewritesTransactions: ACID: isolation and recoveryTransactionsApps and connections: the client side of the databaseApps and connectionsMongoDB: documents, replica sets, shardsMongoDBHow MySQL runs a queryHow MySQL runs a querybuffer pool, undo, redo, binlogHow PostgreSQL runs a queryHow PostgreSQL runs a querynew tuple per update, VACUUM, WALSELECT clause orderSELECT clause orderFROM, WHERE, GROUP BY, HAVING, SELECTJoin algorithmsJoin algorithmsnested loop, hash, merge: pick by sizeExternal merge sortExternal merge sort2N I/Os per pass, merge B−1 runs at onceFile organizationsFile organizationsHeap inserts, sorted ranges, hash findsMySQL JOIN, GROUP, ORDERMySQL JOIN, GROUP, ORDERtemporary table and filesortPostgres JOIN, GROUP, ORDERPostgres JOIN, GROUP, ORDERHashAggregate or Sort, by work_memRelational algebraRelational algebraselect, project, join: tables in, table outSQL joins on rowsSQL joins on rowsOuter joins pad unmatched rows with NULLSQL subqueriesOne NULL makes NOT IN return no rowsTriggers and proceduresTriggers and proceduresPer row; a CALL is not one statementWindow functionsWindow functionsaggregate per row without collapsingOpen the Databases & Storage mind mapDatabases & Storageparse, plan, executeRunning a queryHow MySQL runs a queryHow MySQL runs a querybuffer pool, undo, redo, binlogHow PostgreSQL runs a queryHow PostgreSQL runs a querynew tuple per update, VACUUM, WALSELECT clause orderSELECT clause orderFROM, WHERE, GROUP BY, HAVING, SELECTJoin algorithmsJoin algorithmsnested loop, hash, merge: pick by sizeExternal merge sortExternal merge sort2N I/Os per pass, merge B−1 runs at onceFile organizationsFile organizationsHeap inserts, sorted ranges, hash findsMySQL JOIN, GROUP, ORDERMySQL JOIN, GROUP, ORDERtemporary table and filesortPostgres JOIN, GROUP, ORDERPostgres JOIN, GROUP, ORDERHashAggregate or Sort, by work_memRelational algebraRelational algebraselect, project, join: tables in, table outSQL joins on rowsSQL joins on rowsOuter joins pad unmatched rows with NULLSQL subqueriesOne NULL makes NOT IN return no rowsTriggers and proceduresTriggers and proceduresPer row; a CALL is not one statementWindow functionsWindow functionsaggregate per row without collapsing
This page on the Databases & Storage mind map · 55 pages in 8 branches · open it to see the whole map and test yourself in recall mode.