Using EXISTS, SOME, ANY in SQL

Using EXISTS:

EXISTS takes a subquery as an argument and returns TRUE if the subquery returns anything and FALSE if the result set is empty.

SELECT * FROM Employee

WHERE EXISTS (SELECT * FROM Employee WHERE EmployeeDesc='Manager')

The subquery inside EXISTS is evaluated only once in this example. Because the return from the subquery has at least one row, EXISTS evaluates to TRUE and all the rows in the query are printed.

If you change the subquery as shown next, you don’t get back any results.

SELECT * FROM Employee

WHERE EXISTS (SELECT * FROM Employee WHERE EmployeeDesc='Clerk')

EXISTS evaluates to FALSE. The subquery does not generate a result set

Using ANY:

                SELECT * FROM Employee

WHERE EmployeeDesc = ANY 

(SELECT EmployeeDesc  FROM Employee WHERE EmployeeDesc='Manager')

Using SOME:

SELECT * FROM Employee

WHERE EmployeeDesc = SOME 

(SELECT EmployeeDesc  FROM Employee WHERE EmployeeDesc='Manager')

IN returns the same result as ANY and SOME.

Difference between SOME and ANY:

SOME compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row. SOME must match at least one row in the subquery and must be preceded by comparison operators. Suppose using greater than ( >) with SOME means greater than at least one value.

ANY compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row. 

Using ALL:

ALL returns TRUE only if all the results of a subquery meet the condition.

SELECT * FROM Employee

WHERE EmployeeDesc <> ALL 

(SELECT EmployeeDesc  FROM Employee WHERE EmployeeDesc='Manager')

This statement returns everybody except 'Manager'. <>ALL evaluates to TRUE only if the result set does not contain what is on the left of the <>.