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

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