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.
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.
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). Now30 NOT IN (10, 10, 20)is TRUE (Demo: NOT IN fixed with IS NOT NULL). - Use
NOT EXISTSwith a correlated condition. EXISTS is only ever TRUE or FALSE: a subquery returns a row or it does not. Dee's row does not matche.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 here | Subquery runs | Result |
|---|---|---|
d.id IN (SELECT e.dept_id …) | 1 | Sales, IT |
d.id NOT IN (SELECT e.dept_id …) | 1 | no rows (Dee's NULL) |
NOT IN (… WHERE e.dept_id IS NOT NULL) | 1 | HR |
NOT EXISTS (… WHERE e.dept_id = d.id) | 3 | HR |
DISTINCT … JOIN emp e ON … | none, 12 pairs | 3 rows → Sales, IT |
e.salary > (SELECT AVG … = e.dept_id) | 4 | Ann |
(SELECT COUNT(*) …) AS staff | 3 | Sales 2, IT 1, HR 0 |
e.salary > ANY (dept 10) | 1 | Ann, Cid, Dee |
e.salary > ALL (dept 30, empty) | 1 | all 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.