Using ALL, Some and Any with Sub Queries

In case a sub Query return a single value , here you can write a normal query without using comparison operators like the following an example:

DECLARE @Employees Table (Id int , Name varchar(50) , Salary int)
INSERT INTO @Employees 
SELECT 1 ,'Fadi'  ,  10000  UNION ALL 
SELECT 2 ,'Shadi' ,  20000  UNION ALL 
SELECT 3 ,'Tareq' ,  30000  UNION ALL 
SELECT 4 ,'Rola'  ,  40000  UNION ALL 
SELECT 5 ,'Rana'  ,  50000  UNION ALL 
SELECT 6 ,'Rasha' ,  60000

-- we will use this query as sub Query return a single value
-- it's return the Average of Salary
SELECT AVG(Salary) FROM @Employees

SELECT * FROM @Employees
WHERE Salary > (SELECT AVG(Salary) FROM @Employees)

 The result of the above query:

35000


Id          Name                                               Salary
----------- -------------------------------------------------- -----------
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

What if your Sub Query returns a list of values, in this case you will need to use the comparison operators to compare each value return by a sub query so to simplify these queries let's see the benefits of ALL , Any and Some.

Let's start by ALL by this example:

-- we will use this query as sub Query 
SELECT Salary FROM @Employees WHERE Salary < 40000

SELECT * FROM @Employees
WHERE  Salary > ALL 
(SELECT Salary FROM @Employees WHERE Salary < 40000)
Salary
-----------
10000
20000
30000


Id          Name                                               Salary
----------- -------------------------------------------------- -----------
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

ALL keyword equal to AND comparison operator so it means the values return by a Query must be Salary > 30000 and Salary > 20000 and Salary > 10000.

Note: if the sub query return no rows, a comparison always true so means it will return all rows.

--we will use this query as sub Query 
-- this query will return no rows
SELECT Salary FROM @Employees WHERE Salary > 80000

SELECT * FROM @Employees
WHERE  Salary > ALL (SELECT Salary FROM @Employees WHERE Salary > 80000)
Salary
-----------


Id          Name                                               Salary
----------- -------------------------------------------------- -----------
1           Fadi                                               10000
2           Shadi                                              20000
3           Tareq                                              30000
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

ANY key word equal to OR comparison operator so it means the values return by a Query must be greater than one on any values 30000, 20000 and 10000.

Note: SOME is equal to ANY but SOME (ANSI Standard keyword).

--any one condition true return the value
SELECT * FROM @Employees
WHERE  Salary > ANY (SELECT Salary FROM @Employees WHERE Salary < 40000)

--any one condition true return the value
SELECT * FROM @Employees
WHERE  Salary > SOME (SELECT Salary FROM @Employees WHERE Salary < 40000)
Id          Name                                               Salary
----------- -------------------------------------------------- -----------
2           Shadi                                              20000
3           Tareq                                              30000
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

Note: if the sub query return no rows or null value, a comparison always false so means it will return nothing.

SELECT * FROM @Employees
WHERE  Salary > ANY (SELECT Salary FROM @Employees WHERE Salary > 80000)
Id          Name                                               Salary
----------- -------------------------------------------------- -----------