Chapter 41: PostgreSQL ANY Operator
PostgreSQL: the ANY (and ALL) operator.
Many students see ANY for the first time and think:
“This looks weird — why not just use IN?”
And the honest answer is:
ANY is often cleaner, more flexible, and sometimes faster than IN — especially when you’re comparing against an array or a subquery result.
Let’s go step-by-step like we’re sitting together writing queries.
1. What does ANY actually do? (Plain teacher explanation)
ANY means:
“Is the left value equal to at least one value in the right-hand set (array or subquery result)?”
In other words:
|
0 1 2 3 4 5 6 |
column = ANY (array_or_subquery) |
is exactly the same as:
|
0 1 2 3 4 5 6 |
column = value1 OR column = value2 OR column = value3 OR … |
But written much more elegantly.
ALL is the opposite:
|
0 1 2 3 4 5 6 |
column = ALL (array_or_subquery) |
means:
“Is the left value equal to every single value in the right-hand set?”
(Almost nobody uses = ALL — usually people use NOT IN or <> ALL)
2. The three most common places you will see ANY
| Pattern # | Syntax example | When you use it (real feeling) | Equivalent (longer) form |
|---|---|---|---|
| 1 | column = ANY(ARRAY[1,3,5,7]) | Comparing against a fixed list of values | column IN (1,3,5,7) |
| 2 | column = ANY(VALUES (1),(3),(5)) | Dynamic list from application / prepared statement | column IN (VALUES …) |
| 3 | column = ANY(SELECT id FROM other_table …) | Comparing against result of a subquery | column IN (SELECT id …) |
Important PostgreSQL note (2026 reality):
- = ANY(array) is usually faster than IN (subquery) when the subquery returns many rows
- PostgreSQL optimizer often rewrites IN (subquery) to = ANY(subquery) internally anyway
- But writing = ANY makes your intention clearer when dealing with arrays
3. Real, practical examples (using our college schema)
Example 1: ANY with static array (most common in application code)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Students enrolled in course 1, 2 or 3 SELECT s.first_name || ' ' || s.last_name AS student, s.gpa FROM students s JOIN enrollments e ON e.student_id = s.id WHERE e.course_id = ANY(ARRAY[1, 2, 3]) ORDER BY s.gpa DESC; |
→ Same as e.course_id IN (1,2,3)
But now imagine the list comes from application:
|
0 1 2 3 4 5 6 7 |
-- In Node.js / Python / Java → you pass array as parameter WHERE e.course_id = ANY($1::int[]) -- $1 = [1,2,3] |
→ This is very clean — no string concatenation needed
Example 2: ANY with subquery (very powerful)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Students who have GPA higher than at least one enrolled student in 2025 SELECT first_name || ' ' || last_name AS student, gpa FROM students WHERE gpa > ANY( SELECT gpa FROM students s2 JOIN enrollments e ON e.student_id = s2.id WHERE s2.enrollment_year = 2025 ) ORDER BY gpa DESC; |
→ “Higher than at least one student who actually enrolled in 2025”
Example 3: ANY vs ALL – compare extremes
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Students whose GPA is higher than **every** enrolled student in 2024 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; |
→ This is very rare — most people write it as:
|
0 1 2 3 4 5 6 |
WHERE gpa > (SELECT MAX(gpa) FROM …) |
But > ALL is valid and sometimes clearer.
Example 4: ANY with NOT (anti-pattern – usually avoid)
|
0 1 2 3 4 5 6 7 |
-- NOT = ANY → means NOT IN WHERE course_id <> ANY(ARRAY[1,2,3]) |
→ Same as course_id NOT IN (1,2,3)
But notice:
|
0 1 2 3 4 5 6 |
WHERE course_id <> ALL(ARRAY[1,2,3]) |
is not the same as NOT IN when NULLs are involved — be careful.
4. ANY vs IN – when to prefer which (2026 best practice)
| Situation | Recommended choice | Why / Performance notes |
|---|---|---|
| Small static list (3–10 values) | IN (1,2,3) | More readable |
| List from application array/parameter | = ANY($1::int[]) | Clean, no string building, safe from injection |
| Subquery returns many rows | = ANY(subquery) | Often faster than IN (subquery) |
| Subquery returns small number of rows | IN (subquery) or = ANY | Both fine — readability wins |
| Need to compare with every value (ALL) | > ALL(subquery) or MAX | MAX usually clearer |
Very important performance tip:
|
0 1 2 3 4 5 6 7 |
WHERE column IN (SELECT …) -- can be slow if subquery large WHERE column = ANY(SELECT …) -- PostgreSQL often optimizes better |
5. Quick cheat-sheet (keep in your notes)
| Goal | Recommended ANY pattern | More readable alternative |
|---|---|---|
| Match any value in fixed list | status = ANY(ARRAY[‘pending’,’shipped’]) | status IN (‘pending’,’shipped’) |
| Match any value from array parameter | id = ANY($1::bigint[]) | — |
| Higher than at least one value | gpa > ANY(SELECT gpa FROM …) | gpa > (SELECT MIN(gpa) FROM …) |
| Lower than every value | gpa < ALL(SELECT gpa FROM …) | gpa < (SELECT MAX(gpa) FROM …) |
| Not equal to any value | course_id <> ANY(ARRAY[1,2,3]) | course_id NOT IN (1,2,3) |
Your mini homework right now
- Write query: students whose GPA is higher than at least one enrolled student in 2025 (use > ANY)
- Rewrite it using > (SELECT MIN(…)) → compare readability
- Write query using = ANY(ARRAY[…]) for students enrolled in courses 1 or 3
- Try NOT EXISTS vs <> ALL for students with no enrollments in a certain course
Next class?
Tell me:
- Want ANY vs ALL vs IN performance & EXPLAIN comparison?
- ANY inside UPDATE / DELETE?
- How ANY works with arrays + unnest?
- Or move to CTE, WINDOW functions, LATERAL?
Your teacher is ready — what’s the next topic? 🚀
