ALL, ANY and SOME Comparison Conditions in SQL
ALL, ANY and SOME Comparison Conditions in SQL
ALL
The ALL comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery.
SQL>
select * from [dbo].[Employees] where salary > All (select AVG (Salary) from [Employees] where salary >4000
empid mgrid empname salary
----------- ----------- ------------------------- ---------------------
1 NULL Nancy 10000.00
2 1 Andrew 7000.00
(2 row(s) affected)
If a subquery returns zero rows, the condition evaluates to TRUE. In the following example, the subquery returns zero rows, which means the whole expression "salary > ALL (zero rows)" evaluates to TRUE, so all rows are displayed.
SQL>
select * from [dbo].[Employees] where salary > All (select salary from [Employees] where salary > 25000 )
empid mgrid empname salary
----------- ----------- ------------------------- ---------------------
1 NULL Nancy 10000.00
2 1 Andrew 7000.00
3 1 Janet 5000.00
4 1 Margaret 5000.00
5 2 Steven 2500.00
6 2 Michael 2500.00
7 3 Robert 2500.00
8 3 Laura 2500.00
9 3 Ann 2500.00
10 4 Ina 2500.00
11 7 David 2000.00
12 7 Ron 2000.00
13 7 Dan 2000.00
14 11 James 1500.00
(14 row(s) affected)
ANY
The ANY comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery.
SQL>
select * from [dbo].[Employees] where salary > ANY (select salary From Employees where salary >5000);
empid mgrid empname salary
----------- ----------- ------------------------- ---------------------
1 NULL Nancy 10000.00
(1 row(s) affected)
If a subquery returns zero rows, the condition evaluates to FALSE. In the following example, the subquery returns zero rows, which means the whole expression "salary > ANY (zero rows)" evaluates to FALSE, so no rows are displayed.
SQL>
select * from [dbo].[Employees] where salary > ANY (select salary From Employees where salary >10000);
empid mgrid empname salary
----------- ----------- ------------------------- ---------------------
(0 row(s) affected)
SOME
The SOME and ANY comparison conditions do exactly the same thing and are completely interchangeable.
Comments
Post a Comment