MySQL ALL Keyword
The MySQL ALL keyword is used with WHERE and HAVING clause. It returns TRUE if all of the subquery values satisfies the condition.
Syntax
The syntax for using ALL keyword in MySQL is given below:
SELECT column1, column2, column3, ... FROM table_name WHERE column_name operator ALL (SELECT column1, column2, ... FROM table_name WHERE condition);
Note: The operator mentioned above must be a standard comparison operator (=, <>, !=, <, <=, > or >=).
Example:
Consider a database containing tables called Employee and Bonus_Paid with the following records:
| EmpID | Name | City | Age | Salary |
|---|---|---|---|---|
| 1 | John | London | 25 | 3000 |
| 2 | Marry | New York | 24 | 2750 |
| 3 | Jo | Paris | 27 | 2800 |
| 4 | Kim | Amsterdam | 30 | 3100 |
| 5 | Ramesh | New Delhi | 28 | 3000 |
| 6 | Huang | Beijing | 28 | 2800 |
Table 2: Bonus_Paid table
| EmpID | Bonus |
|---|---|
| 1 | 500 |
| 2 | 400 |
| 3 | 450 |
| 4 | 550 |
| 5 | 400 |
| 6 | 600 |
-
To select all employees from Employee table whose bonus is less than 500, the query is given below.
SELECT * FROM Employee WHERE EmpID <> ALL (SELECT EmpID FROM Bonus_Paid WHERE Bonus >= 500);
The subquery will produce following result:
EmpID 1 4 6 The query then compares EmpID values with all subquery values and fetches only those results which return true with all subquery values. It will produce the following result:
EmpID Name City Age Salary 2 Marry London 24 2750 3 Jo London 27 2800 5 Ramesh New Delhi 28 3000
❮ MySQL Keywords
