ALL keyword in SQL server

In SQL Server, ALL is a comparison keyword used with a subquery. It compares a value against every value returned by the subquery. The condition is TRUE only if it satisfies the comparison for ALL rows. Think of ALL as AND logic across the subquery results. ALL must always be used with a comparison operator like >, <, >=, <=, or =.

Syntex

expression operator ALL (subquery)

For example, Management wants to find employees whose salary is greater than ALL salaries in the IT department. In simple words: find employees who earn more than the highest IT salary.

See the demo

CREATE TABLE Employees (     EmpID   INT,     EmpName VARCHAR(50),     Dept    VARCHAR(20),     Salary  INT );   INSERT INTO Employees VALUES (1,'Amit','IT',50000), (2,'Bagesh','IT',60000), (3,'Neha','HR',55000), (4,'Pooja','HR',70000), (5,'Rahul','Sales',45000);

See the records into the table

Below is the query to get the list

SELECT * FROM Employees WHERE Salary > ALL (     SELECT Salary     FROM Employees     WHERE Dept = 'IT' );

See the result

SQL evaluates this condition like this: Salary must be: greater than 50,000 AND greater than 60,000. Only employees earning more than the maximum IT salary qualify. So effectively, ALL works like: Salary > (SELECT MAX(Salary) FROM Employees WHERE Dept = 'IT')

 

Here Only one employee qualifies —the HR employee earning 70,000. That’s because 70,000 is greater than every IT salary.

The ALL keyword compares a value with all values returned by a subquery and returns true only if the condition is satisfied for every value.