SQL ANY vs ALL Operators

🔹 1. The ANY Operator

Compares a value to the result of a subquery.
Returns TRUE if the condition is satisfied for at least one value in the subquery.

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE department_id = 2
);

👉 Finds employees who earn more than at least one person in department 2.

Also compares a value to the result of a subquery.
Returns TRUE only if the condition is satisfied for every value in the subquery.

SELECT name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE department_id = 2
);

👉 Finds employees who earn more than every employee in department 2 (higher than the max salary in department 2).

Aspect	ANY	ALL
Meaning	TRUE if condition matches at least one value	TRUE if condition matches every value
Typical Use Case	Compare against the smallest value in a set (similar to MIN)	Compare against the largest value in a set (similar to MAX)
Performance	May stop scanning once a match is found	Must evaluate the entire subquery result
NULL Handling	NULL values can prevent matches—clean your data	Any NULL in the subquery can make the comparison UNKNOWN

Imagine a products table:

SELECT *
FROM products
WHERE price > ANY (
  SELECT price
  FROM products
  WHERE category = 'A'
);

✅ Returns products with price > 100 (or any value in category A). Product 3 is included.

SELECT *
FROM products
WHERE price > ALL (
  SELECT price
  FROM products
  WHERE category = 'A'
);

✅ Returns products with price > 200. Only products priced higher than everyone in category A are returned.

💡 Tip: Use indexes on the filtered columns inside subqueries to keep ANY and ALL comparisons performant, especially on large tables.