ALL
ALL
What ALL Really Means
x > ALL (subquery) is TRUE only if x is greater than every single row the subquery returns — equivalently, greater than the subquery's maximum value. This is a strict, demanding comparison: x has to clear the highest bar in the set, not just any bar.
This is the direct contrast to ANY (17.7): ANY is satisfied by beating the weakest competitor; ALL requires beating the strongest one.
Sample data used throughout this chapter:
employees
| id | name | department | salary | manager_id |
|---|---|---|---|---|
| 1 | Alice | Engineering | 95000 | NULL |
| 2 | Bob | Engineering | 72000 | 1 |
| 3 | Carol | Engineering | 68000 | 1 |
| 4 | Dave | Sales | 60000 | 5 |
| 5 | Eve | Sales | 88000 | NULL |
| 6 | Frank | Sales | 55000 | 5 |
| 7 | Grace | Marketing | 70000 | NULL |
| 8 | Heidi | Marketing | 62000 | 7 |
departments
| id | name | budget |
|---|---|---|
| 1 | Engineering | 300000 |
| 2 | Sales | 200000 |
| 3 | Marketing | 150000 |
| 4 | HR | 100000 |
Worked Example
sqlSELECT name, salary FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Marketing');
Marketing salaries are 70000 (Grace) and 62000 (Heidi); the maximum is 70000. salary > ALL (...) is equivalent to salary > 70000.
Checking all 8 employees against 70000:
| name | salary | > 70000? |
|---|---|---|
| Alice | 95000 | yes |
| Bob | 72000 | yes |
| Carol | 68000 | no |
| Dave | 60000 | no |
| Eve | 88000 | yes |
| Frank | 55000 | no |
| Grace | 70000 | no (not strictly greater than itself) |
| Heidi | 62000 | no |
Result, ordered by salary descending: Alice (95000), Eve (88000), Bob (72000) — 3 rows.
ANY vs ALL, Side by Side
Same subquery (Marketing salaries), same outer comparison direction, wildly different result sizes:
salary > ANY (...)→ 5 rows (beats the weakest Marketing salary, 62000)salary > ALL (...)→ 3 rows (beats the strongest Marketing salary, 70000)
This is exactly why mixing them up is a real bug, not just a style choice — swapping ANY for ALL (or vice versa) silently changes which rows qualify, with no error to warn you.
Translating ALL to Plain English
| Form | Plain-English equivalent |
|---|---|
x = ALL (subq) | true only if the subquery returns one row (or zero) equal to x — rarely useful with = |
x > ALL (subq) | x > MAX(subq) |
x < ALL (subq) | x < MIN(subq) |
x <> ALL (subq) | same as x NOT IN (subq) |
Edge Cases
- If the subquery returns zero rows,
x > ALL (...)is always TRUE — vacuously true, since there's no row that fails to satisfy the condition ("greater than every one of zero rows" is trivially satisfied). This is the mirror opposite of> ANY's behavior on an empty set (always FALSE) — an easy point to mix up. <> ALLis equivalent toNOT IN, and inherits the exact same NULL danger discussed in 17.6 — a NULL in the subquery can make the whole thing UNKNOWN.
Key Takeaways / Q&A
Q: Is `salary > ALL (subquery)` a strict or permissive condition? A: Strict — it must beat every row, i.e., the maximum.
Q: What does `> ALL` reduce to? A: > MAX(subquery).
Q: What happens when the ALL subquery is empty? A: The condition becomes vacuously TRUE — the opposite of ANY's FALSE-on-empty behavior.
Q: Someone says "ANY means it just has to match anything, so it should be the stricter one." Correct this. A: Backwards — ANY is the weak/permissive one (beats the minimum); ALL is the strict one (beats the maximum).