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);

Example:

Consider a database containing tables called Employee and Bonus_Paid with the following records:

Table 1: Employee table

Table 2: Bonus_Paid table

• 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