MySQL ANY and ALL Operators

The ANY and ALL operators in MySQL are used with subqueries to compare a value against a set of values returned by the inner query. They help filter rows based on whether any or all values in the subquery meet a condition.

ANY Operator

ANY returns TRUE if the comparison holds true for at least one value in the subquery result. It is similar to checking "does this value match any of these?"

Syntax

SELECT column FROM table
WHERE column operator ANY (SELECT column FROM table WHERE condition);

ALL Operator

ALL returns TRUE only if the comparison holds true for every value in the subquery result. It is similar to checking "does this value beat all of these?"

Syntax

SELECT column FROM table
WHERE column operator ALL (SELECT column FROM table WHERE condition);

Sample Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES (1, 'Ravi Kumar', 'IT', 72000);
INSERT INTO employees VALUES (2, 'Sneha Joshi', 'HR', 45000);
INSERT INTO employees VALUES (3, 'Karan Das', 'IT', 88000);
INSERT INTO employees VALUES (4, 'Pooja Nair', 'Finance', 61000);
INSERT INTO employees VALUES (5, 'Amit Roy', 'HR', 53000);
INSERT INTO employees VALUES (6, 'Deepa Rao', 'Finance', 67000);

Example: ANY — Employees Earning More Than Any HR Employee

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

HR salaries are 45000 and 53000. ANY means: salary must be greater than at least one of these values (i.e., greater than 45000).

Result:

emp_name    | salary
------------+--------
Ravi Kumar  | 72000.00
Karan Das   | 88000.00
Pooja Nair  | 61000.00
Amit Roy    | 53000.00
Deepa Rao   | 67000.00

Example: ALL — Employees Earning More Than All HR Employees

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

ALL means: salary must be greater than both 45000 and 53000 — so greater than 53000.

Result:

emp_name    | salary
------------+--------
Ravi Kumar  | 72000.00
Karan Das   | 88000.00
Pooja Nair  | 61000.00
Deepa Rao   | 67000.00

ANY vs ALL Summary

= ANY vs IN

These two queries produce the same result:

-- Using = ANY
SELECT emp_name FROM employees
WHERE department = ANY (SELECT department FROM employees WHERE salary > 70000);

-- Using IN
SELECT emp_name FROM employees
WHERE department IN (SELECT department FROM employees WHERE salary > 70000);

Key Points

• ANY returns TRUE if the condition is met by at least one value in the subquery.
• ALL returns TRUE only if the condition is met by every value in the subquery.
• = ANY is equivalent to IN.
• > ANY means greater than the minimum value in the subquery.
• > ALL means greater than the maximum value in the subquery.