Using IN, ANY, and ALL Queries

Randy Sims SQL Fundamentals I

ANY and ALL queries are related to IN queries in that they are used for multi-row returns. They also allow comparisons to be made (equal, not equal, greater than, less than, etc.).

The IN Condition

Reviewing an IN condition in a query first, we know that the field on the left side of the IN must be found as a result of the field(s) returned in the subquery or list.

An IN condition allows you to use a subquery or list in your query as a multi-row equals sign. Below, you see 7 rows are returned for the number of employees that have a salary of 9000 and 9500.

Next, you see that the salary for people who’s first name begins with the letters ‘Dan’ have a salary of 9000 and 9500. In the third query, I use that as a subquery to get a count of people who make a salary of 9000 and 9500.

SELECT count(*) 
FROM hr.employees 
WHERE salary IN (9000,9500);

 COUNT(*)
----------
 7
1 row selected.

SELECT salary 
FROM hr.employees 
WHERE first_name like 'Dan%';

 SALARY
----------
 9000
 9500

2 rows selected.

SELECT count(*) 
FROM hr.employees 
WHERE salary IN 
  (SELECT salary 
   FROM hr.employees 
   WHERE first_name LIKE 'Dan%');

COUNT(*)
----------
 7
1 row selected.

The ALL Condition

The ALL condition is like the IN condition except you can add a comparison. For the following query, we are using our Dan people as a comparison again, who have salaries of either 9000 or 9500.

The question answered is, “How many people make more than the Dan-like people?” salary is greater than ALL (those Dan-like salaries). Run the following and see that all of the salaries are more than 9000 and 9500.

--The salaries that are greater than 
--ALL the salaries of Dan-like people
--9000 or 9500

SELECT count(*)
FROM hr.employees
WHERE salary > ALL (SELECT salary
 FROM hr.employees
 WHERE first_name LIKE 'Dan%');

 COUNT(*)
----------
 20
1 row selected.

--The salaries that are greater than or equal to
--ALL the salaries of Dan-like people
--9000 or 9500

SELECT count(*)
FROM hr.employees
WHERE salary >= ALL (SELECT salary
 FROM hr.employees
 WHERE first_name LIKE 'Dan%');

 COUNT(*)
----------
 23
1 row selected.

--The salaries that are less than or equal to
--ALL the salaries of Dan-like people
--9000 or 9500

SELECT count(*)
FROM hr.employees
WHERE salary <= ALL (SELECT salary
                     FROM hr.employees
                     WHERE first_name LIKE 'Dan%');

 COUNT(*)
----------
 84
1 row selected.

The ANY Condition

The ANY condition is like the IN condition except you can add a comparison. The ANY condition will return true for ANY value that applies.

For the following query, we are using our Dan people as a comparison again, who have salaries of either 9000 or 9500.

The question answered is, “How many people make more than any of the Dan-like people?” salary is greater than ANY of (those Dan-like salaries). So, if someone makes 9500, their salary is greater than ANY (9000 or 9500) of the Dan-like salaries because it applies to any of them.

Danielle Greene makes 9500.
Daniel Faviet makes 9000.

Danielle never made the list before because the salary had to be greater than ALL of the Dan-like salaries (9000 and 9500). Now it is greater than ANY of the Dan-like salaries. 9500 is greater than one of the salaries, so now Danielle makes the list along with two others who also make 9500.

SELECT count(*)
FROM hr.employees
WHERE salary > ANY (SELECT salary
                   FROM hr.employees
                   WHERE first_name LIKE 'Dan%');

 COUNT(*)
----------
 23
1 row selected.

--Check it out:

SELECT first_name, salary
 FROM hr.employees
 WHERE salary > ANY (SELECT salary
                     FROM hr.employees
                     WHERE first_name LIKE 'Dan%')
minus
SELECT first_name, salary
 FROM hr.employees
 WHERE salary > ALL (SELECT salary
                     FROM hr.employees
                     WHERE first_name LIKE 'Dan%');

FIRST_NAME           SALARY
-------------------- ----------
Danielle             9500
David                9500
Patrick              9500

3 rows selected.

Tagged: WHERE