Multi Row Subqueries: IN, ANY and ALL

Concept

When a subquery returns many rows, the outer query needs an operator that accepts a set: IN, ANY (also spelled SOME) or ALL.

Example

-- IN: employees in any department located in Pune or Mumbai
SELECT first_name, dept_id
FROM   employees
WHERE  dept_id IN (SELECT id FROM departments WHERE location IN ('Pune', 'Mumbai'));

-- ANY: paid more than at least one person in department 10
SELECT first_name, salary
FROM   employees
WHERE  salary > ANY (SELECT salary FROM employees WHERE dept_id = 10);

-- ALL: paid more than everyone in department 10
SELECT first_name, salary
FROM   employees
WHERE  salary > ALL (SELECT salary FROM employees WHERE dept_id = 10);

Explanation

ANY and ALL are worth reading twice. > ANY means "greater than the minimum", because being greater than at least one member is easiest to satisfy against the smallest. > ALL means "greater than the maximum". Most people find > (SELECT MAX(...)) clearer, and it is exactly equivalent - with one exception noted below.

The NOT IN trap

-- Returns NOTHING, because one employee has dept_id = NULL
SELECT name FROM departments
WHERE  id NOT IN (SELECT dept_id FROM employees);

-- Fix 1: exclude the NULLs
SELECT name FROM departments
WHERE  id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);

-- Fix 2, preferred: NOT EXISTS is NULL safe by construction
SELECT d.name FROM departments d
WHERE  NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

x NOT IN (10, 20, NULL) expands to x <> 10 AND x <> 20 AND x <> NULL. The last term is always UNKNOWN, so the whole expression can never be TRUE. This affects <> ALL identically, since it is the same thing.

Empty sets behave differently

-- Against an empty set:
--   x IN  (empty) is FALSE - nothing matches
--   x > ALL (empty) is TRUE  - vacuously, there is nothing to exceed
--   x > ANY (empty) is FALSE - there is nothing to beat
SELECT first_name FROM employees
WHERE  salary > ALL (SELECT salary FROM employees WHERE dept_id = 999);

That query returns every employee, which surprises people. > (SELECT MAX(salary) ... WHERE dept_id = 999) returns no rows instead, because MAX of nothing is NULL. This is the one case where ALL and MAX genuinely differ.

Important rules

• IN with NULLs in the list is safe. NOT IN with even one NULL returns no rows.
• = ANY is IN; <> ALL is NOT IN.
• Against an empty set, ALL is true and ANY is false.
• The subquery must return exactly one column for IN, unless the dialect supports row constructors: WHERE (a, b) IN (SELECT x, y FROM ...) works in MySQL and PostgreSQL.

Common mistakes

• NOT IN against a nullable column - the classic silent empty result.
• Reading > ANY as "greater than all of them".
• Assuming an empty subquery makes > ALL return nothing.
• Using IN with a huge subquery result when a join or EXISTS would be far cheaper.

Best practices

• Default to EXISTS and NOT EXISTS for existence questions; use IN for genuinely small, known lists.
• Write > (SELECT MAX(...)) instead of > ALL when readability matters - and be aware of the empty set difference.
• Always add WHERE col IS NOT NULL inside a NOT IN subquery if you keep the form.
• Make the subquery column NOT NULL at the schema level where the domain allows it.

Practice

1. Find customers who have placed at least one shipped order, using IN.
2. Find the product that is more expensive than every product in the service category, using both ALL and MAX.
3. Demonstrate the NOT IN NULL trap on the sample data and fix it twice.