✏️ Explanatory Question
Level: Advanced — These comparison operators work with subqueries that return multiple values.
These operators compare a single value against a set of values returned by a subquery, combined with a comparison operator (>, <, =, etc.).
ANY — they behave identically (SOME is the ANSI standard name).= ANY is equivalent to IN. > ALL means "greater than the maximum", and > ANY means "greater than the minimum" of the returned set.
| Expression | Meaning | Equivalent |
|---|---|---|
> ANY (set) |
Greater than at least one | > MIN(set) |
> ALL (set) |
Greater than every value | > MAX(set) |
< ANY (set) |
Less than at least one | < MAX(set) |
< ALL (set) |
Less than every value | < MIN(set) |
= ANY (set) |
Equals at least one | IN (set) |
-- > ANY: employees earning more than the LOWEST salary in dept 10
SELECT name, salary FROM employees
WHERE salary > ANY (
SELECT salary FROM employees WHERE dept_id = 10
);
-- > ALL: employees earning more than the HIGHEST salary in dept 10
SELECT name, salary FROM employees
WHERE salary > ALL (
SELECT salary FROM employees WHERE dept_id = 10
);
-- = ANY: same as IN
SELECT name FROM employees
WHERE dept_id = ANY (
SELECT dept_id FROM departments WHERE location = 'Kolkata'
);
-- SOME behaves exactly like ANY
SELECT name, salary FROM employees
WHERE salary > SOME (
SELECT salary FROM employees WHERE dept_id = 20
);