Chapter 42: PostgreSQL ALL Operator
The ALL operator.
Many students see ALL for the first time and think:
“This is just the opposite of ANY, right? So why do people barely use it?”
And the honest answer is:
Yes, it is the logical opposite — but in real-world queries people almost always prefer clearer alternatives (MAX/MIN, NOT EXISTS, etc.)
So ALL is valid, correct, and sometimes elegant, but rarely the first choice in production code.
Let’s go through it carefully, step-by-step.
1. What does the ALL operator actually do?
ALL means:
“Is the left value compared to every single value in the right-hand set (array or subquery result)?”
In other words:
|
0 1 2 3 4 5 6 |
column > ALL (array_or_subquery) |
means:
|
0 1 2 3 4 5 6 |
column > value1 AND column > value2 AND column > value3 AND … |
i.e.
“column is greater than every value in the list / subquery”
The same applies to < ALL, = ALL, <= ALL, >= ALL, <> ALL
2. Most common comparison patterns with ALL
| Comparison | Meaning (in plain English) | Equivalent clearer expression (what most people write instead) |
|---|---|---|
| > ALL | greater than every value in the set | > (SELECT MAX(…) FROM …) |
| < ALL | smaller than every value in the set | < (SELECT MIN(…) FROM …) |
| = ALL | equal to every value in the set | very rare — means all values in subquery are identical |
| <> ALL | different from every value in the set | NOT IN (subquery) or <> ANY (but careful with NULLs) |
3. Real, practical examples (using our college schema)
Example 1: Students with GPA higher than every enrolled student in 2024
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT first_name || ' ' || last_name AS student, gpa FROM students WHERE gpa > ALL ( SELECT gpa FROM students s2 JOIN enrollments e ON e.student_id = s2.id WHERE s2.enrollment_year = 2024 ) ORDER BY gpa DESC; |
Result (example):
|
0 1 2 3 4 5 6 7 8 9 10 |
student_name | gpa ---------------|------ Meera Iyer | 10.00 Priya Reddy | 9.60 Sneha Kumar | 9.20 |
→ These students have GPA strictly higher than every single student who enrolled in 2024
More readable & usually faster alternative (what 95% of developers write):
|
0 1 2 3 4 5 6 7 8 9 10 11 |
WHERE gpa > ( SELECT MAX(gpa) FROM students s2 JOIN enrollments e ON e.student_id = s2.id WHERE s2.enrollment_year = 2024 ) |
Example 2: Courses cheaper than all courses taken by a particular student
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT c.name AS course_name, c.price FROM courses c WHERE c.price < ALL ( SELECT b.price FROM books b JOIN order_items oi ON oi.book_id = b.id JOIN orders o ON o.id = oi.order_id WHERE o.customer_id = 1 -- customer Rahul ) ORDER BY c.price; |
→ “Courses cheaper than every book Rahul ever bought”
Clearer alternative:
|
0 1 2 3 4 5 6 7 8 9 10 |
WHERE c.price < ( SELECT MIN(b.price) FROM books b … same joins … ) |
Example 3: = ALL (very rare — only useful in specific cases)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Find students who have exactly the same grades in all their courses -- (very artificial example) SELECT s.first_name FROM students s WHERE s.id = 1 AND 'A+' = ALL ( SELECT grade FROM enrollments WHERE student_id = s.id ); |
→ Means every grade of student 1 is ‘A+’
→ Almost nobody writes this — usually you do:
|
0 1 2 3 4 5 6 |
HAVING MIN(grade) = MAX(grade) AND MIN(grade) = 'A+' |
4. ALL vs ANY vs IN — quick decision table (2026 reality)
| Requirement | Recommended choice | Why / When to prefer it |
|---|---|---|
| Equals at least one value | = ANY(…) or IN (…) | ANY better with arrays, IN more readable |
| Greater than at least one value | > ANY(…) | Very useful |
| Greater than every value | > ALL(…) or > (SELECT MAX) | MAX usually clearer & often faster |
| Not equal to any value | <> ANY(…) or NOT IN (…) | Careful with NULLs — NOT IN can be dangerous |
| Equal to every value | = ALL(…) | Very rare — prefer MIN=MAX |
| Subquery returns many rows | = ANY(subquery) | Often faster than IN (subquery) |
Important NULL behavior warning (very common trap):
|
0 1 2 3 4 5 6 |
WHERE column <> ALL (subquery) |
If any value in the subquery is NULL → whole condition becomes NULL (false)
This is why NOT IN (subquery) is dangerous when subquery can return NULL — prefer NOT EXISTS
5. Quick cheat-sheet (keep in your notes)
| Goal | Recommended ALL / ANY pattern | Clearer / safer alternative |
|---|---|---|
| Greater than every value | gpa > ALL(SELECT gpa FROM …) | gpa > (SELECT MAX(gpa) FROM …) |
| Smaller than every value | price < ALL(SELECT price FROM …) | price < (SELECT MIN(price) FROM …) |
| Equals at least one value | status = ANY(ARRAY[‘A’,’A+’]) | status IN (‘A’,’A+’) |
| Not equal to any value | course_id <> ALL(ARRAY[1,2,3]) | course_id NOT IN (1,2,3) |
| Every value is the same | grade = ALL(SELECT grade FROM …) | MIN(grade) = MAX(grade) |
Your mini homework right now
- Write query: students whose GPA is higher than every enrolled student in 2024 (use > ALL)
- Rewrite it using > (SELECT MAX(…)) — which one do you find more readable?
- Write query: courses cheaper than every book a specific customer bought (use < ALL)
- Try = ALL on grades — see what happens when grades are different
Next class?
Tell me:
- Want ANY / ALL vs IN / EXISTS performance comparison (EXPLAIN ANALYZE)?
- How ANY / ALL behave with arrays + unnest?
- ANY inside UPDATE / DELETE (very powerful pattern)?
- Or move to CTE, WINDOW functions, LATERAL joins?
Your teacher is ready — what’s the next topic? 🚀
