SQL / SUBQUERIES AND CTES
ANY and ALL against a list of values
Compare one value against a whole set with ANY and ALL, read them as OR and AND chains, and predict their NULL and empty-set results.
What you will learn
- Read x > ANY (s) as an OR chain and x > ALL (s) as an AND chain over the set
- Translate > ANY to "above the minimum" and > ALL to "above the maximum"
- Swap = ANY for IN and <> ALL for NOT IN, including their shared NULL trap
- Predict ANY as false and ALL as true when the subquery returns no rows
Understanding ANY and ALL against a list of values
ANY and ALL sit between a comparison operator and a set of values: salary > ANY (SELECT ...) means the comparison must hold for at least one value in the set, and salary > ALL (SELECT ...) means it must hold for every value. The reliable way to read them is as an expansion, so with the set {5000, 7000} the first becomes salary > 5000 OR salary > 7000 and the second becomes salary > 5000 AND salary > 7000. SOME is an exact synonym for ANY, and the right side has to be a subquery or a table constructor: salary > ANY (5000, 7000) is a syntax error, because that is a parenthesised expression, not a set.
Once you see the OR and AND chains, the ordered comparisons collapse into min and max. > ANY behaves like > (SELECT min(...)) because the smallest value in the set is the easiest one to beat, while > ALL behaves like > (SELECT max(...)) because the largest one is the one that decides the AND chain. Flip the operator and the roles swap: < ANY means below the maximum, < ALL means below the minimum. Equality is the other pair worth knowing by heart, since = ANY is exactly IN and <> ALL is exactly NOT IN.
The same expansion explains the two behaviours that catch people out. An OR chain is true as soon as one comparison is true but only unknown if the remaining ones are unknown, and an AND chain is false as soon as one comparison is false but otherwise unknown, so a single NULL in the set turns > ALL and <> ALL into unknown instead of true, and in a WHERE clause unknown discards the row just like false. When the subquery returns nothing there is nothing to OR or AND together, so ANY is false for every row and ALL is true for every row, which is how a filter meaning "above everything" can quietly match the entire table.
CREATE TABLE employee (name text, dept int, salary int);
INSERT INTO employee VALUES
('Ada', 1, 9000),
('Bo', 1, 4800),
('Cyd', 2, 7000),
('Dee', 2, 5000),
('Eli', 3, 6500);
SELECT name,
salary,
salary > ANY (SELECT salary FROM employee WHERE dept = 2) AS beats_one,
salary > ALL (SELECT salary FROM employee WHERE dept = 2) AS beats_all
FROM employee
WHERE dept <> 2
ORDER BY name;ANY and ALL are shorthand for an OR chain and an AND chain of the same comparison across every value in the set, and all their other behaviour follows from that expansion.
Worked examples
= ANY is IN, <> ALL is NOT IN
Shows that the IN and NOT IN keywords are shorthand for quantified comparisons over a list of values.
CREATE TABLE student (name text, grade text);
INSERT INTO student VALUES ('Ann','A'), ('Ben','C'), ('Cal','F'), ('Dot','B');
SELECT name,
grade,
grade = ANY (VALUES ('A'), ('B')) AS top_grade,
grade <> ALL (VALUES ('A'), ('B')) AS not_top
FROM student
ORDER BY name;Example explained
Line 1grade = ANY (VALUES ('A'), ('B')) expands to grade = 'A' OR grade = 'B', which is what IN ('A','B') means.
Line 2grade <> ALL (VALUES ('A'), ('B')) expands to grade <> 'A' AND grade <> 'B', which is what NOT IN ('A','B') means.
Line 3The VALUES clause supplies a one-column table, satisfying the rule that ANY and ALL need a set on the right rather than a comma-separated expression.
Line 4The two columns are exact opposites here only because = and <> are opposites; = ALL and <> ANY are a completely different pair of conditions.
A NULL in the set
Demonstrates how one NULL value makes an ALL comparison unknown while an ANY comparison can still be true.
CREATE TABLE bid (bidder text, amount int);
INSERT INTO bid VALUES ('a', 300), ('b', NULL), ('c', 500);
SELECT 600 > ALL (SELECT amount FROM bid) AS above_all,
600 > ANY (SELECT amount FROM bid) AS above_any,
200 > ANY (SELECT amount FROM bid) AS above_any_200;Example explained
Line 1600 > ALL finds 600 > 300 and 600 > 500 both true, but 600 > NULL is unknown, so the AND chain never reaches true and returns NULL, printed as a blank cell.
Line 2600 > ANY is true because 600 > 300 already satisfies the OR chain, so the NULL cannot change the result.
Line 3200 > ANY has no true comparison and one unknown one, so the OR chain is unknown rather than false.
Line 4In a WHERE clause an unknown result removes the row, which is why comparing with ALL against a nullable column can return zero rows even when qualifying rows exist.
When the set is empty
Shows the opposite defaults ANY and ALL fall back to when the subquery produces no rows.
CREATE TABLE product (name text, price int, discontinued boolean);
INSERT INTO product VALUES ('mug', 8, false), ('pen', 3, false), ('cap', 15, false);
SELECT name, price
FROM product
WHERE price > ALL (SELECT price FROM product WHERE discontinued)
ORDER BY name;
SELECT count(*) AS any_matches
FROM product
WHERE price > ANY (SELECT price FROM product WHERE discontinued);Example explained
Line 1No row has discontinued set to true, so the subquery returns an empty set of prices.
Line 2price > ALL over an empty set is an AND over nothing, which is true, so all three products pass the filter including the cheapest one.
Line 3price > ANY over the same empty set is an OR over nothing, which is false, so the count query returns 0.
Line 4The pen priced at 3 appearing in a "more expensive than every discontinued item" report is the practical symptom of this rule.
Important notes
SOME and ANY are the same keyword to the parser; the subquery after either must return a single column unless you compare against a row constructor.
Support varies: PostgreSQL also accepts = ANY (ARRAY[...]) for a literal list, MySQL accepts only subqueries after ANY and ALL, and SQLite has no ANY or ALL at all, so use IN, min or max there.
Common mistakes
Writing salary > ANY (5000, 7000) as if it were a value list: the parser rejects it, because ANY and ALL require a subquery, a VALUES table, or in PostgreSQL an array.
Reading > ANY as "greater than every value": the query then only filters on the minimum, so almost every row qualifies and the result looks reasonable while being wrong.
Using <> ALL or NOT IN against a column that contains NULL: the AND chain evaluates to unknown for every row and the query returns nothing at all.
Try it yourself
Change, predict, then run
Create race (runner text, time_sec int) with five rows, one of which has a NULL time_sec, and include a runner called 'ann'. Run SELECT runner FROM race WHERE time_sec < ALL (SELECT time_sec FROM race WHERE runner <> 'ann'), then add AND time_sec IS NOT NULL to the subquery and account for the change in row count.
Open the SQL workspaceCheck your understanding
Table product holds exactly three rows with prices 10, 20 and 30. Query A is SELECT count(*) FROM product WHERE price > ANY (SELECT price FROM product), and query B is the same with > ALL. What are the two counts?
- A is 3 and B is 1, since every price beats something and the highest price beats them all
- A is 2 and B is 0, since only 20 and 30 exceed the minimum and nothing exceeds the maximum
- A is 0 and B is 3, since ANY requires every comparison to hold and ALL requires only one
- A is 3 and B is 3, since each price is compared only against the other two rows
Show answer
> ANY reduces to > min(price), so 20 and 30 qualify and 10 does not, giving 2. > ALL reduces to > max(price), and no price is greater than 30, giving 0. The tempting answer is B = 1: the subquery is not correlated and does not exclude the current row, so 30 is compared with 30 and 30 > 30 is false.