Comparison Operators ANY / ALL

Comparison Operators ANY / ALL

Both Any and All are used in combination of comparison operators (for example =, <>, !=). 'ALL' indicates that the value must match all the values in a set. 'ANY' will evaluate to True if the expression matches any value in the set.

Lets understand operators Any and All with examples:

Create a table Salary:

Create table salary (
name varchar (20),
emp_sal int );

Insert data into table Salary:

insert into salary values ('a', 100)
insert into salary values ('b', 70)
insert into salary values ('b', 200)
insert into salary values ('d', 300)
insert into salary values ('e', 140)
insert into salary values ('f', null)

Verify if data inserted is correct:

Select * from Salary

name	emp_sal
a	100
b	70
b	200
d	300
e	140
f	Null

Lets take a look at few examples of comparison operators ANY / ALL:

Example 1

In below example, the subquery results in values 70 and 200. Outer query finds rows from table Salary where emp_sal is greater than both 70 and 200. So it results in only one row where emp_sal is 300.

'>ALL' means greater than every value or greater than the maximum value.

Select * from salary
where emp_sal > All (Select emp_sal from salary where name = 'b')

name	emp_sal
d	300

Example 2

Similarly below we are running the same query (as above) but with '>ANY'.

'>ANY' means greater than at least one value or greater than the minimum value. Hence the result below

Select * from salary
where emp_sal > Any (Select emp_sal from salary where name = 'b')

name	emp_sal
a	100
b	200
d	300
e	140

Few more examples related to 'All' and 'Any':

Example 3

Select * from salary
where emp_sal < All (Select emp_sal from salary where name = 'b')

no rows will be returned

Example 4

Select * from salary
where emp_sal < Any (Select emp_sal from salary where name = 'b')

name	emp_sal
a	100
b	70
e	140

Example 5

Select * from salary
where emp_sal > Any (Select emp_sal from salary where name in ('f', 'b'))

name	emp_sal
a	100
b	200
d	300
e	140

Example 6

Select * from salary
where emp_sal > All (Select emp_sal from salary where name in ('f', 'b'))

no rows will be returned