ANY and ALL in MySQL

The ANY and ALL operators in MySQL are used with subqueries to compare values from a column against a set of values returned by the subquery.

1. ANY Operator

• ANY returns TRUE if at least one value from the subquery matches the condition.
• Works with comparison operators like =, >, <, >=, <=, <>.

Syntax

SELECT column_name FROM table_name WHERE column_name operator ANY (subquery);

Example: Find employees earning more than at least one employee in the HR department

SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'HR');

✅ Returns employees who have a salary higher than at least one HR employee.
❌ Does not require the salary to be higher than all HR employees.

Equivalent to Using OR

SELECT * FROM employees WHERE salary > (SELECT MIN(salary) FROM employees WHERE department = 'HR');

2. ALL Operator

• ALL returns TRUE only if all values from the subquery satisfy the condition.
• The condition must be true for every value in the subquery result.

Syntax

SELECT column_name FROM table_name WHERE column_name operator ALL (subquery);

Example: Find employees earning more than all HR employees

SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'HR');

✅ Returns employees whose salary is higher than every HR employee's salary.
❌ If even one HR employee has a higher salary, the condition fails.

Equivalent to Using MAX

SELECT * FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department = 'HR');

ANY vs. ALL Comparison Table

Use Cases

✅ ANY is useful when comparing against at least one value.
✅ ALL is useful when checking against every value in a subquery.