Subqueries: IN, EXISTS, ANY and ALL

A subquery is a SELECT inside another statement, and it comes in three shapes: a scalar subquery returns one value, a row subquery returns one row, and a table subquery returns a set of rows that IN, EXISTS, ANY and ALL test. A correlated subquery refers to a column of the outer query, so logically it runs again for every outer row. EXISTS only asks whether at least one row comes back, so it is always TRUE or FALSE. IN, ANY and ALL compare a value with every row and follow three-valued logic: x IN S means x = ANY S, x NOT IN S means x <> ALL S, ALL over an empty set is TRUE, ANY over an empty set is FALSE, and a single NULL among the rows can turn a FALSE or a TRUE into UNKNOWN. These few rules decide when NOT IN, NOT EXISTS and a join return the same rows.

This lesson uses the rules of the three-valued logic lesson throughout.

Three shapes of subquery

The shape decides where a subquery may stand. A scalar subquery can go wherever a value can, a row subquery is compared with a row of values, and a table subquery is the right-hand side of IN, EXISTS, ANY or ALL. Five students and five results:

-- Five students and their results; Esha (105) has not taken any test yet.
CREATE TABLE students (roll INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL);
CREATE TABLE results (roll INTEGER NOT NULL, course TEXT NOT NULL, marks INTEGER);
INSERT INTO students VALUES (101, 'Aarav', 'CSE'), (102, 'Diya', 'ECE'), (103, 'Kabir', 'CSE'),
                            (104, 'Meera', 'ME'), (105, 'Esha', 'ECE');
INSERT INTO results VALUES (101, 'CS201', 91), (101, 'MA101', 78), (102, 'CS201', 64),
                           (103, 'CS201', 55), (104, 'ME105', 88);

-- A scalar subquery gives one value for each outer row; with no rows, the value is NULL.
SELECT s.name, quote((SELECT max(r.marks) FROM results AS r WHERE r.roll = s.roll)) AS best
FROM students AS s
ORDER BY s.roll;

-- A scalar subquery that returns two rows: SQLite takes the first one; PostgreSQL stops with an error.
SELECT (SELECT r.marks FROM results AS r WHERE r.roll = 101) AS one_of_two;

-- A row subquery compares two columns at once: the first ECE student by roll number.
SELECT name FROM students
WHERE (dept, roll) = (SELECT dept, min(roll) FROM students WHERE dept = 'ECE' GROUP BY dept);

┌───────┬──────┐
│ name  │ best │
├───────┼──────┤
│ Aarav │ 91   │
│ Diya  │ 64   │
│ Kabir │ 55   │
│ Meera │ 88   │
│ Esha  │ NULL │
└───────┴──────┘
┌────────────┐
│ one_of_two │
├────────────┤
│ 91         │
└────────────┘
┌──────┐
│ name │
├──────┤
│ Diya │
└──────┘

• A scalar subquery per outer row. Each student’s best mark is computed by a subquery that refers to s.roll, a column of the outer query. Esha has no results, so her subquery returns no rows and its value is NULL, which both engines specify (SQLite: subquery expressions, PostgreSQL 18: scalar subqueries).
• Two rows where one was expected. Student 101 has two results. SQLite takes the first row the subquery returns and prints 91 without complaint. PostgreSQL treats a scalar subquery with more than one row as an error, and stops with “more than one row returned by a subquery used as an expression”. The SQLite answer depends on which row happens to come first, so a query that relies on it is a bug waiting for new data.
• A row subquery compares the pair (dept, roll) with one row: the first ECE student by roll number, Diya.

EXISTS, correlation and semi-joins

EXISTS (subquery) is TRUE if the subquery returns at least one row and FALSE otherwise. What the rows contain does not matter, which is why EXISTS (SELECT 1 …) is the usual spelling, and NULLs in them do not matter either: EXISTS is never UNKNOWN (PostgreSQL 18: subquery expressions). When the subquery is correlated, the outer row’s values act as constants during each evaluation, so the question is asked once per outer row.

CREATE TABLE students (roll INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL);
CREATE TABLE results (roll INTEGER NOT NULL, course TEXT NOT NULL, marks INTEGER);
INSERT INTO students VALUES (101, 'Aarav', 'CSE'), (102, 'Diya', 'ECE'), (103, 'Kabir', 'CSE'),
                            (104, 'Meera', 'ME'), (105, 'Esha', 'ECE');
INSERT INTO results VALUES (101, 'CS201', 91), (101, 'MA101', 78), (102, 'CS201', 64),
                           (103, 'CS201', 55), (104, 'ME105', 88);

-- Students with a result of 70 or more, three ways. EXISTS and IN return each student once.
SELECT 'EXISTS' AS form, group_concat(name, ', ') AS students FROM (
  SELECT s.name FROM students AS s
  WHERE EXISTS (SELECT 1 FROM results AS r WHERE r.roll = s.roll AND r.marks >= 70)
  ORDER BY s.roll)
UNION ALL
SELECT 'IN', group_concat(name, ', ') FROM (
  SELECT s.name FROM students AS s
  WHERE s.roll IN (SELECT r.roll FROM results AS r WHERE r.marks >= 70)
  ORDER BY s.roll)
UNION ALL
-- A join returns one row per matching result, so Aarav appears twice.
SELECT 'JOIN', group_concat(name, ', ') FROM (
  SELECT s.name FROM students AS s JOIN results AS r ON r.roll = s.roll
  WHERE r.marks >= 70
  ORDER BY s.roll);

-- NOT EXISTS: the students with no result at all.
SELECT s.name FROM students AS s
WHERE NOT EXISTS (SELECT 1 FROM results AS r WHERE r.roll = s.roll);

┌────────┬─────────────────────┐
│  form  │      students       │
├────────┼─────────────────────┤
│ EXISTS │ Aarav, Meera        │
│ IN     │ Aarav, Meera        │
│ JOIN   │ Aarav, Aarav, Meera │
└────────┴─────────────────────┘
┌──────┐
│ name │
├──────┤
│ Esha │
└──────┘

EXISTS and IN both return Aarav and Meera, once each: a student either has a result of 70 or more or does not. The join returns Aarav twice, because it produces one row per matching pair and Aarav has two such results. In algebra, EXISTS is a semi-join: it keeps the rows of the left table that have a partner on the right, without copying anything from the right. NOT EXISTS is an anti-join, which keeps the rows without a partner: Esha, the only student with no results. Questions with “every”, such as the students who took every course, nest two NOT EXISTS; the division lesson builds them step by step.

“Once per outer row” is the meaning, not the plan. EXPLAIN QUERY PLAN shows how SQLite runs the two forms of the question (SQLite: EXPLAIN QUERY PLAN):

CREATE TABLE students (roll INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE results (roll INTEGER NOT NULL, course TEXT NOT NULL, marks INTEGER);

-- A correlated EXISTS and the IN form of the same question, as SQLite 3.49.1 plans them.
EXPLAIN QUERY PLAN
SELECT s.name FROM students AS s
WHERE EXISTS (SELECT 1 FROM results AS r WHERE r.roll = s.roll AND r.marks >= 70);

EXPLAIN QUERY PLAN
SELECT s.name FROM students AS s
WHERE s.roll IN (SELECT r.roll FROM results AS r WHERE r.marks >= 70);

┌────┬────────┬─────────┬──────────────────────────────┐
│ id │ parent │ notused │            detail            │
├────┼────────┼─────────┼──────────────────────────────┤
│ 2  │ 0      │ 216     │ SCAN s                       │
│ 5  │ 0      │ 0       │ CORRELATED SCALAR SUBQUERY 1 │
│ 9  │ 5      │ 216     │ SCAN r                       │
└────┴────────┴─────────┴──────────────────────────────┘
┌────┬────────┬─────────┬──────────────────────────────────────────────┐
│ id │ parent │ notused │                    detail                    │
├────┼────────┼─────────┼──────────────────────────────────────────────┤
│ 2  │ 0      │ 91      │ SEARCH s USING INTEGER PRIMARY KEY (rowid=?) │
│ 6  │ 0      │ 0       │ LIST SUBQUERY 1                              │
│ 9  │ 6      │ 216     │ SCAN r                                       │
│ 17 │ 6      │ 0       │ CREATE BLOOM FILTER                          │
└────┴────────┴─────────┴──────────────────────────────────────────────┘

For EXISTS it scans the students and runs the correlated subquery for each one (CORRELATED SCALAR SUBQUERY). For IN it builds the list of qualifying roll numbers once (LIST SUBQUERY) and looks each roll number up through the primary key; the Bloom filter in the plan is a quick test that rules out most numbers that cannot match. Same rows, different work: optimisers rewrite subqueries into joins and lists whenever they can prove the result is unchanged.

IN, NOT IN and the NULL that empties a result

x IN (subquery) is TRUE if some row equals x, FALSE if none does (an empty subquery included), and UNKNOWN if none is equal but some comparison is UNKNOWN. NOT IN is its negation, so it is TRUE only when every row is known to be different from x. PostgreSQL’s manual states both rules, and that IN is equivalent to = ANY and NOT IN to <> ALL (PostgreSQL 18: subquery expressions). The consequence, with one payment that arrived without a roll number:

-- Fee payments. One payment came in without a roll number and is still unmatched.
CREATE TABLE students (roll INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE payments (receipt INTEGER PRIMARY KEY, roll INTEGER, amount INTEGER NOT NULL);
INSERT INTO students VALUES (101, 'Aarav'), (102, 'Diya'), (103, 'Kabir'), (104, 'Meera');
INSERT INTO payments VALUES (1, 101, 45000), (2, 103, 45000), (3, NULL, 45000);

-- Who has not paid? NOT IN returns nobody: for 102 and 104 the condition is UNKNOWN.
SELECT 'NOT IN' AS form, count(*) AS unpaid FROM students
WHERE roll NOT IN (SELECT roll FROM payments)
UNION ALL
-- NOT EXISTS ignores the payment without a roll number.
SELECT 'NOT EXISTS', count(*) FROM students AS s
WHERE NOT EXISTS (SELECT 1 FROM payments AS p WHERE p.roll = s.roll)
UNION ALL
-- NOT IN with the NULLs filtered out of the list gives the same answer as NOT EXISTS here.
SELECT 'NOT IN, NULLs removed', count(*) FROM students
WHERE roll NOT IN (SELECT roll FROM payments WHERE roll IS NOT NULL);

SELECT s.roll, s.name FROM students AS s
WHERE NOT EXISTS (SELECT 1 FROM payments AS p WHERE p.roll = s.roll);

┌───────────────────────┬────────┐
│         form          │ unpaid │
├───────────────────────┼────────┤
│ NOT IN                │ 0      │
│ NOT EXISTS            │ 2      │
│ NOT IN, NULLs removed │ 2      │
└───────────────────────┴────────┘
┌──────┬───────┐
│ roll │ name  │
├──────┼───────┤
│ 102  │ Diya  │
│ 104  │ Meera │
└──────┴───────┘

NOT IN finds nobody. For Diya, 102 NOT IN (101, 103, NULL) is 102 <> 101 AND 102 <> 103 AND 102 <> NULL, which is TRUE AND TRUE AND UNKNOWN: UNKNOWN, and WHERE drops her. One NULL in the subquery’s column makes NOT IN FALSE or UNKNOWN for every row. NOT EXISTS asks a different question, “is there a payment row for this roll number?”, and the NULL row is never a match, so it returns Diya and Meera. Filtering the NULLs out of the subquery makes NOT IN agree.

NOT IN and NOT EXISTS return the same rows only when neither side can be NULL: no NULL in the subquery’s column, and none in the outer value. A NULL on the outer side breaks them apart too: if roll itself were NULL, NOT IN would be UNKNOWN for any non-empty subquery, while NOT EXISTS would be TRUE, because p.roll = s.roll is never TRUE. For “rows with no match”, NOT EXISTS says what is meant; declaring the key columns NOT NULL makes both forms safe.

ANY and ALL, with empty sets and NULLs

x op ANY (subquery) is TRUE if the comparison is TRUE for at least one row, FALSE if it is TRUE for none (an empty subquery included), and UNKNOWN if none is TRUE but at least one is UNKNOWN. x op ALL (subquery) is TRUE if the comparison is TRUE for every row (an empty subquery included), FALSE if it is FALSE for at least one, and UNKNOWN if none is FALSE but at least one is UNKNOWN (PostgreSQL 18: subquery expressions). These are OR and AND over all the rows, with FALSE and TRUE as the results for zero rows, so they follow the lowest-and-highest rule of three-valued logic.

SQLite has no ANY or ALL with a subquery, so the example below rebuilds both from EXISTS, using IS TRUE, IS FALSE and IS NULL to look at each comparison’s truth value. It prints what PostgreSQL’s ANY and ALL return for the same values, next to the common rewrite with max:

-- SQLite has no ANY or ALL comparison with a subquery.
SELECT 5 > ALL (SELECT 1);

-- Sets of values to compare with; the set named '{}' has no members at all.
CREATE TABLE members (set_name TEXT, v INTEGER);
INSERT INTO members VALUES ('{1, 2}', 1), ('{1, 2}', 2), ('{1, NULL}', 1), ('{1, NULL}', NULL);
CREATE TABLE cases (k INTEGER, x INTEGER, set_name TEXT);
INSERT INTO cases VALUES (1, 5, '{}'), (2, 5, '{1, 2}'), (3, 5, '{1, NULL}'), (4, 0, '{1, NULL}');
CREATE TABLE tv (t INTEGER, name TEXT);
INSERT INTO tv VALUES (1, 'TRUE'), (0, 'FALSE'), (NULL, 'UNKNOWN');

-- x > ALL (S): FALSE if some comparison is FALSE, else UNKNOWN if some is UNKNOWN, else TRUE (also for an empty S).
-- x > ANY (S): TRUE if some comparison is TRUE, else UNKNOWN if some is UNKNOWN, else FALSE (also for an empty S).
-- Both built from EXISTS, next to the common rewrite x > (SELECT max(v) ...).
SELECT c.x, c.set_name AS s,
  CASE WHEN EXISTS (SELECT 1 FROM members AS m WHERE m.set_name = c.set_name AND (c.x > m.v) IS FALSE) THEN 'FALSE'
       WHEN EXISTS (SELECT 1 FROM members AS m WHERE m.set_name = c.set_name AND (c.x > m.v) IS NULL) THEN 'UNKNOWN'
       ELSE 'TRUE' END AS "x > ALL (s)",
  CASE WHEN EXISTS (SELECT 1 FROM members AS m WHERE m.set_name = c.set_name AND (c.x > m.v) IS TRUE) THEN 'TRUE'
       WHEN EXISTS (SELECT 1 FROM members AS m WHERE m.set_name = c.set_name AND (c.x > m.v) IS NULL) THEN 'UNKNOWN'
       ELSE 'FALSE' END AS "x > ANY (s)",
  (SELECT tv.name FROM tv WHERE tv.t IS NOT DISTINCT FROM
     (c.x > (SELECT max(m.v) FROM members AS m WHERE m.set_name = c.set_name))) AS "x > max(s)"
FROM cases AS c
ORDER BY c.k;

┌───┬───────────┬─────────────┬─────────────┬────────────┐
│ x │     s     │ x > ALL (s) │ x > ANY (s) │ x > max(s) │
├───┼───────────┼─────────────┼─────────────┼────────────┤
│ 5 │ {}        │ TRUE        │ FALSE       │ UNKNOWN    │
│ 5 │ {1, 2}    │ TRUE        │ TRUE        │ TRUE       │
│ 5 │ {1, NULL} │ UNKNOWN     │ TRUE        │ TRUE       │
│ 0 │ {1, NULL} │ FALSE       │ UNKNOWN     │ FALSE      │
└───┴───────────┴─────────────┴─────────────┴────────────┘

Parse error near line 2: near "ALL": syntax error

• 5 > ALL ({}) is TRUE and 5 > ANY ({}) is FALSE. With no rows, no comparison is FALSE and none is TRUE.
• 5 > ALL ({1, NULL}) is UNKNOWN: 5 > 1 is TRUE, 5 > NULL is UNKNOWN, and no comparison is FALSE.
• 0 > ALL ({1, NULL}) is FALSE: 0 > 1 is FALSE, which settles it whatever the NULL says.
• 5 > ANY ({1, NULL}) is TRUE, settled by 5 > 1, and 0 > ANY ({1, NULL}) is UNKNOWN.

The last column is the trap. x > (SELECT max(v) …) looks like x > ALL, but max of no rows is NULL, so over the empty set it gives UNKNOWN where ALL gives TRUE, and max ignores NULLs, so over {1, NULL} it gives TRUE where ALL gives UNKNOWN. The two agree only on sets that are not empty and hold no NULL, like {1, 2}.

Rewrites that keep the answer, and rewrites that change it

Most subquery bugs are rewrites that look equivalent and are not. In a WHERE clause, where only TRUE keeps a row:

• x IN (SELECT y …) to EXISTS (… WHERE y = x) keeps the same rows in a WHERE clause. Used as a value, they differ: IN can be UNKNOWN, EXISTS never is.
• x IN (SELECT y …) to a join on y = x keeps the same rows only when y is unique, such as a key. When y repeats, the join returns one row per match.
• x NOT IN (SELECT y …) to NOT EXISTS (… WHERE y = x) keeps the same rows only when neither x nor y can be NULL. A NULL y empties NOT IN, and with a NULL x only NOT EXISTS keeps the row.
• x > ALL (SELECT y …) to x > (SELECT max(y) …) keeps the same rows only when the subquery returns rows and no NULLs among them.
• x = ANY (SELECT y …) to x IN (SELECT y …) is always safe: they are the same predicate.

When a join must replace IN, for example to read columns of the other table, put DISTINCT on the result or join to a subquery that returns each key once. When NOT IN must stay, add WHERE y IS NOT NULL inside the subquery. The exact WHERE form of x > ALL (S) with EXISTS is NOT EXISTS (SELECT 1 FROM S WHERE (x > y) IS NOT TRUE): it keeps a row only when every comparison is TRUE, which is what WHERE x > ALL (S) keeps.

Interview questions

Fresher · What is the difference between a correlated and an uncorrelated subquery, and how does a database run each?

An uncorrelated subquery does not refer to the outer query, so its result is the same for every outer row and can be computed once, such as WHERE roll IN (SELECT roll FROM payments). A correlated subquery refers to a column of the outer query, such as WHERE EXISTS (SELECT 1 FROM results AS r WHERE r.roll = s.roll), so its result can differ from row to row, and logically it is evaluated again for each outer row. That is the meaning; the plan may differ: optimisers often turn a correlated EXISTS into a semi-join, or build a list or hash table once, as long as the rows returned are the same. A good answer adds that EXISTS is never UNKNOWN, while IN can be.

Key takeaways

• A scalar subquery returns one value, NULL when it finds no row. With several rows PostgreSQL raises an error and SQLite silently takes the first.
• A correlated subquery is evaluated once per outer row in meaning; optimisers may decorrelate it into a join.
• EXISTS is TRUE or FALSE, never UNKNOWN; it is a semi-join, and NOT EXISTS an anti-join.
• IN is = ANY and NOT IN is <> ALL. One NULL in the subquery makes NOT IN return no rows.
• ALL over an empty set is TRUE, ANY over an empty set is FALSE, and NULLs make both UNKNOWN unless a TRUE (for ANY) or a FALSE (for ALL) settles them.
• x > (SELECT max …) differs from x > ALL for empty sets and NULLs; a join differs from IN when the matched column repeats.