T-SQL - ALL Keyword
The T-SQL (Transact-SQL) 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 T-SQL (Transact-SQL) 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
❮ T-SQL Keywords
