Alright, let’s talk about ANY and ALL. These are the operators you use when you want to compare a single value not to just one other value, but to a whole set of values from a subquery. Think of them as the SQL way of saying, “Is my value bigger than any of those?” or “Is my value smaller than all of those?” They’re incredibly powerful, but they also have a weird, clunky syntax that feels like it was designed by someone who’d just finished a very long and tedious standards committee meeting. We’ll get through it together.

The key to understanding these is to remember they’re just shorthand for a series of comparisons using OR (for ANY) or AND (for ALL). SQL is basically doing the looping for you.

The Anatomy of ANY and ALL

The syntax is always: <Your_Column> <Comparison_Operator> ANY|ALL (<Subquery>)

The <Comparison_Operator> is crucial. It can be =, !=, >, >=, <, or <=. The meaning changes completely based on which one you pick. = ANY is a totally different beast from > ALL.

Let’s use a classic example. Imagine a products table and a order_items table. We want to find products that have never been ordered. One way to do this is to find a product whose id is not equal to any of the product_ids in the order_items table. But that’s not quite right. If it’s not equal to any one of them, it could still be equal to another. We need the opposite: we need products whose id is not equal to all of them. This is the mental shift you have to make.

-- Find products that have never been ordered
SELECT *
FROM products p
WHERE p.id <> ALL (
    SELECT product_id
    FROM order_items
);

Read that WHERE clause out loud: “Where the product ID is not equal to all of the IDs in the order items.” If it’s not equal to every single one, then it’s definitely not in the list. This is logically equivalent to NOT IN, which we’ll come back to.

ANY is for “At Least One”, ALL is for “Every Single One”

This is the core concept. ANY returns true if the comparison is true for at least one value in the subquery result. ALL returns true only if the comparison is true for every single value in the subquery result.

Let’s make this concrete with numbers. Suppose your subquery returns the set (1, 5, 10).

  • 5 = ANY (subquery) is TRUE because 5 equals at least one value (5).
  • 5 > ANY (subquery) is TRUE because 5 is greater than at least one value (1).
  • 5 < ALL (subquery) is FALSE because while 5 is less than 10, it is not less than every value (it’s not less than 1 or 5).
  • 11 > ALL (subquery) is TRUE because 11 is greater than every single value (1, 5, and 10).

The Pitfalls: NULLs and Empty Sets

Here’s where the designers’ questionable choices, or at least the logical consequences of set theory, come to bite us. You must understand what happens with NULL and empty sets.

NULLs are party crashers. If your subquery returns any NULL values, ALL gets weird. Let’s say the subquery returns (1, 2, NULL). 3 > ALL (subquery) evaluates to 3 > 1 (true), 3 > 2 (true), and 3 > NULL (which is NULL). The rule is: ALL only returns TRUE if all comparisons are true. If any are NULL, the overall result is NULL, which is treated as FALSE in a WHERE clause. So your seemingly true condition suddenly becomes false. The fix? Ensure your subquery filters out NULLs or re-think your logic.

The Empty Set is a philosophical nightmare. What if your subquery returns no rows? What is 5 > ALL (nothing)? Logically, it’s vacuously true. There are no rows to prove the statement false, so ALL returns TRUE. Conversely, 5 = ANY (nothing) returns FALSE because there is no row to prove the statement true. This can absolutely wreck your day if a subquery you expect to return data suddenly goes empty. Your ALL condition will start returning everything, which is almost certainly not what you want. Always consider the edge case of an empty result set.

You can often rewrite ANY and ALL using more common operators. This isn’t just an academic exercise; sometimes the alternatives are clearer or perform better.

  • = ANY(subquery) is identical to IN (subquery). Use IN. It’s more readable. Everyone knows IN.
  • <> ALL(subquery) is identical to NOT IN (subquery). But beware! This inherits the dreaded NULL problem of NOT IN. If the subquery contains any NULL values, the entire NOT IN (and therefore <> ALL) expression evaluates to NULL/FALSE. It’s a trap.
  • <> ANY(subquery) is not the same as NOT IN. It means “not equal to at least one value.” This is almost always true unless you’re comparing against a set of identical values. It’s rarely useful.
  • > ANY can often be rewritten with MIN: value > ANY (subquery) is the same as value > (SELECT MIN(...) FROM ...).
  • > ALL can often be rewritten with MAX: value > ALL (subquery) is the same as value > (SELECT MAX(...) FROM ...).

The MIN/MAX rewrite is a best practice you should strongly consider. Why? Unless you’re correlated subquery, the database can run that SELECT MIN(...) once, which is often much more efficient than comparing your value against every single row in the subquery’s result, especially for large sets. It’s a clear performance win.

So, when should you actually use ANY/ALL? Primarily when your comparison operator isn’t equality. Needing to find rows where a value is greater than the maximum of another set is a perfect, clear use case for > ALL. Just be mindful of the NULL and empty set pitfalls. For equality checks, just use IN and NOT IN (with appropriate NULL handling). Your future self, and anyone else reading your code, will thank you for the clarity.