Right, so you’ve got a scalar value and an array. You want to see how they relate. Do they match? Is one bigger? This is where ANY and ALL saunter in, looking all innocent but packing a serious punch. They are your primary tools for conducting these comparisons, and they are deceptively simple until you hit the first edge case. Let’s demystify them.

The core idea is this: ANY and ALL are used with a boolean operator (=, >, <, <>, etc.) to compare a single value to every element in an array. ANY means “is the comparison true for at least one element in the array?” It’s the existential quantifier. ALL means “is the comparison true for every single element in the array?” It’s the universal quantifier.

Think of it like this: 42 = ANY (array) asks “Is 42 in this array?” Meanwhile, 42 > ALL (array) asks “Is 42 bigger than every element in this array?”

The Basic Syntax and Semantics

You’ll always use them in a WHERE clause or a conditional statement. The structure is:

scalar_value operator ANY (array_expression)
scalar_value operator ALL (array_expression)

Let’s see some live ammunition. Imagine a table of video game scores.

CREATE TABLE player_scores (
    player_id INT,
    high_scores INT[] -- An array of their top scores
);

-- Insert some data
INSERT INTO player_scores VALUES
(1, ARRAY[1025, 987, 1555]),
(2, ARRAY[750, 822]),
(3, ARRAY[1100]),
(4, ARRAY[500, 600, 700, 800]); -- Someone who is steadily improving

Now, find every player who has ever scored over 1000. This is a job for ANY.

SELECT * FROM player_scores
WHERE 1000 < ANY (high_scores);

This returns players 1 and 3. For player 1, 1000 < 1555 is true, so ANY is satisfied. It doesn’t matter that 1000 is not less than 987 or 1025. It only needs one win. Player 3’s single score qualifies. Player 2’s best is 822, and Player 4’s is 800, so they’re out.

Now, let’s find the truly consistent players: those whose lowest score is still above 500. If the lowest score is above 500, then all scores must be above 500.

SELECT * FROM player_scores
WHERE 500 < ALL (high_scores);

This returns players 1, 2, and 3. Player 4 has a score of exactly 500, so 500 < 500 is false, causing ALL to fail. The ALL operator is brutally strict. A single false result in the entire array sinks the whole ship.

The Dreaded Empty Array Trap

Here’s where the designers’ choices get… interesting. Let’s talk about edge cases that will bite you if you’re not careful.

What happens with an empty array '{}'? The logical behavior is actually defined by standard SQL, but it feels counterintuitive.

  • WHERE scalar_value operator ANY ('{}') is always false. You’re asking “Is there any element in this empty list that matches?” There aren’t any elements, so the answer is no. This makes sense.
  • WHERE scalar_value operator ALL ('{}') is always true. Wait, what? Yes, you read that right. You’re asking “Is this true for every element in this empty list?” Since there are no elements that could violate the condition, the statement is vacuously true. It’s a logic puzzle. Think of it like this: “All unicorns in this room can fly” is technically true because there are no unicorns to disprove the statement.

This means you must always consider the possibility of empty arrays in your data model. If a player_scores row could have a high_scores array that is empty, then a query like WHERE 1000 < ALL (high_scores) would return that player! Because the condition is vacuously true. This is almost never what you actually want.

The best practice? Be paranoid. If a column can contain empty arrays, you need to explicitly filter them out or handle them with COALESCE.

-- A safer query to find players whose lowest score is above 500
SELECT * FROM player_scores
WHERE
    high_scores <> '{}' AND -- Filter out empty arrays first!
    500 < ALL (high_scores);

Why Not Just Use IN?

You might look at = ANY(...) and think “Hey, that’s just IN (...)!”. And you’d be almost right.

-- These two queries are functionally identical:
SELECT * FROM player_scores WHERE 1555 = ANY (high_scores);
SELECT * FROM player_scores WHERE 1555 IN (SELECT unnest(high_scores));

So why use ANY? Two reasons:

  1. Performance and Clarity: The = ANY(array) syntax is native to the array type. It’s the most direct and often the most efficient way to express the operation. It tells the planner exactly what you mean without involving a subquery.
  2. Versatility: IN is literally only for equality (=). ANY and ALL work with any operator. You can do > ANY, <= ALL, <> ALL, etc. This is their superpower.

<> ALL (array) is particularly useful. It means “not equal to any element in the array,” which is the opposite of = ANY. It’s a clean way to check for exclusion.

-- Find players who have never scored a 750
SELECT * FROM player_scores WHERE 750 <> ALL (high_scores);

In the end, ANY and ALL are like specialized scalpels. Incredibly powerful for the specific job of scalar-to-array comparison, but you need a steady hand and an awareness of their sharp edges—especially that vacuum of truth surrounding the empty array. Use them deliberately, and always test with null and empty array cases. Your future, debugging-self will thank you.