MySQL ANY & ALL

ANY and ALL are operators that compare a single value against a set of values returned by a subquery. They are always paired with a comparison operator (>, <, =, >=, <=, <>).

---

ANY

ANY returns TRUE if the comparison is true for at least one value in the subquery result. SOME is a synonym for ANY and can be used interchangeably.

value operator ANY (subquery)

For example, to find products that are cheaper than at least one product in the "Electronics" category:

SELECT name, price
FROM products
WHERE price < ANY (
    SELECT price FROM products WHERE category = 'Electronics'
);

This returns any product whose price is less than the maximum price in the Electronics category — because as long as the price is below any one value in the set, the condition is true.

---

ALL

ALL returns TRUE only if the comparison is true for every single value in the subquery result.

value operator ALL (subquery)

Find products that are cheaper than every product in the "Electronics" category:

SELECT name, price
FROM products
WHERE price < ALL (
    SELECT price FROM products WHERE category = 'Electronics'
);

This returns products whose price is less than the minimum Electronics price — the product must be cheaper than all of them.

---

ANY vs ALL Quick Reference

Operator	Meaning	Equivalent to
> ANY	Greater than at least one value	> MIN(subquery)
> ALL	Greater than every value	> MAX(subquery)
< ANY	Less than at least one value	< MAX(subquery)
< ALL	Less than every value	< MIN(subquery)
= ANY	Equal to at least one value	Same as IN (subquery)
<> ALL	Not equal to any value	Same as NOT IN (subquery)

---

Using = ANY as IN

= ANY behaves exactly like IN. These two queries are identical:

-- Using = ANY
SELECT name FROM customers
WHERE city = ANY (SELECT city FROM branch_offices);

-- Equivalent using IN
SELECT name FROM customers
WHERE city IN (SELECT city FROM branch_offices);

---

ALL with Empty Subquery

If the subquery returns no rows, ALL evaluates to TRUE for every outer row (vacuous truth). ANY with an empty subquery evaluates to FALSE. Keep this edge case in mind when the inner table might be empty.

---

Key Points to Remember

• ANY is true if the condition holds for at least one value from the subquery. SOME is an alias.
• ALL is true only if the condition holds for every value from the subquery.
• Both must be paired with a comparison operator: >, <, =, >=, <=, or <>.
• = ANY is equivalent to IN. <> ALL is equivalent to NOT IN.
• If the subquery is empty, ANY returns FALSE and ALL returns TRUE.