SQL ALL Operator
The ALL operator in SQL is used to compare a single value against a set of values. It returns TRUE only if the condition is satisfied for all values in the list or subquery.
Syntax:
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE column_name operator ALL (value1, value2, ...);- operator Can be =, !=, >, <, >=, <=
- The comparison will return TRUE only if the condition holds true for every value in the list
Employee Table
| ID | EmpName | Salary |
|---|---|---|
| 1 | Diya | 15000 |
| 2 | Preeti | 30000 |
| 3 | Prabhu | 40000 |
| 4 | Arya | 20000 |
Example: Using All Operator
Goal: List employees whose salary is greater than 15000 and 20000.
Output:
SELECT *
FROM Emp
WHERE Salary > ALL (15000, 20000);
Output:
| ID | EmpName | Salary |
|---|---|---|
| 2 | Preeti | 30000 |
| 3 | Prabhu | 40000 |
Explanation:
This condition means:
"Show employees whose salary is greater than both 15000 and 20000".
So only those earning more than 20000 are included.
Key Point
- Compare a value with a list or subquery
- Commonly used with > ALL, < ALL, = ALL
- Ensures strict filtering when all values must be met