T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - ALL Operator



The T-SQL (Transact-SQL) ALL Operator 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 operator 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:

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Bonus_Paid table

EmpIDBonus
1500
2400
3450
4550
5400
6600

  • 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:

    EmpIDNameCityAgeSalary
    2MarryLondon242750
    3JoLondon272800
    5RameshNew Delhi283000

❮ T-SQL - Operators