ANY and ALL Operators

What are ANY and ALL?

ANY and ALL compare a single value against a set of values (from a subquery or array).

• ANY (or SOME): Returns true if the comparison is true for at least one value in the set.
• ALL: Returns true only if the comparison is true for every value in the set.

Syntax

SELECT * FROM table_name
WHERE column > ANY (SELECT column FROM other_table);

SELECT * FROM table_name
WHERE column > ALL (SELECT column FROM other_table);

When to Use

• Comparing a value against multiple possibilities (ANY as an alternative to IN)
• Ensuring a value is greater/less than all values in a set
• Working with array comparisons

Key Points

1. ANY = IN — col = ANY(subquery) is equivalent to col IN (subquery).
2. ALL with > — col > ALL(subquery) means col is greater than the maximum of the set.
3. ANY with > — col > ANY(subquery) means col is greater than the minimum of the set.
4. Empty Set — ALL returns TRUE for empty sets. ANY returns FALSE for empty sets.
5. Arrays — Works with arrays too: col = ANY(ARRAY[1,2,3]).