Subqueries With ANY, ALL, and IN
Compare a value against a set using > ANY, > ALL, and IN subqueries.
Overview
Set-comparison operators let a single value be tested against a subquery's
results: > ANY means "greater than at least one", > ALL means "greater than
every", and IN means "equal to any". Here we find employees paid more than the
lowest salary in Engineering (> ANY) versus more than the highest in
Engineering (> ALL).
The Query
Employees & Departments
Loading database engine...
Step-by-Step Breakdown
- Subquery returns all Engineering salaries.
salary > ANY (...)— true if the employee earns more than at least one engineer (i.e. more than the lowest engineer salary).- Swap
ANYforALLto require beating every engineer's salary instead.
IN is = ANY
col IN (subquery) is equivalent to col = ANY (subquery). Use IN for
equality membership and ANY/ALL for ordered comparisons.
Variations
Employees earning more than every Finance employee:
Employees & Departments
Loading database engine...
Common Mistakes
- NULLs break ALL/ANY. If the subquery returns a
NULL,> ALLcan yield unknown; guard withWHERE salary IS NOT NULLin the subquery. - Confusing ANY and ALL.
> ANY= beat the minimum;> ALL= beat the maximum. Easy to invert by accident. - Empty subquery.
> ALL (empty)is true;> ANY (empty)is false — know the empty-set behavior.