SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server - ALL Operator



The SQL Server (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 SQL Server (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

❮ SQL Server - Operators