Skip to main content
Quantified comparison operators compare a scalar value against the rows returned by a subquery using a <comparison> operator combined with an ANY or ALL quantifier. The subquery must return a single column.
  • ANY returns TRUE if the comparison is true for at least one row returned by the subquery.
  • ALL returns TRUE if the comparison is true for every row returned by the subquery.

​
Syntax

<value> <comparison> ANY(<subquery>)
<value> <comparison> ALL(<subquery>)

​
Parameters

ParameterDescriptionSupported input types
<value>A scalar value to compare against each row of the subquery result.Any comparable type
<comparison>A comparison operator: =, <>, !=, <, >, <=, or >=.See Comparison operators
<subquery>A parenthesized SELECT statement that returns exactly one column. The subquery may be correlated with the outer query.Any query that projects a single column of a comparable type
The type of <value> and the column type returned by <subquery> must be comparable.

​
Return type

BOOLEAN

​
Relationship to IN and NOT IN

Quantified comparison subqueries generalize IN and NOT IN:
  • <value> = ANY(<subquery>) is equivalent to <value> IN (<subquery>).
  • <value> <> ALL(<subquery>) is equivalent to <value> NOT IN (<subquery>).
Use ANY and ALL when you need a comparison other than equality (for example, <, >=, or <>) against the rows of a subquery.

​
NULL handling

The result follows SQL three-valued logic:
  • If the subquery returns no rows, ANY returns FALSE and ALL returns TRUE.
  • For ANY: returns TRUE as soon as a matching row is found, even if other rows are NULL. If no row matches and at least one row is NULL (or the comparison itself is NULL), the result is NULL. Otherwise the result is FALSE.
  • For ALL: returns FALSE as soon as a non-matching row is found, even if other rows are NULL. If every row matches and at least one row is NULL (or the comparison itself is NULL), the result is NULL. Otherwise the result is TRUE.

​
Examples

The examples below use the following tables. 
CREATE TABLE employees (
    employee_id INT,
    name TEXT,
    department_id INT,
    salary NUMERIC(8, 2)
);

INSERT INTO employees VALUES
    (100, 'Steven',  90, 24000.00),
    (101, 'Neena',   90, 17000.00),
    (102, 'Lex',     90, 17000.00),
    (103, 'Alex',    60,  9000.00),
    (104, 'Bruce',   60,  6000.00),
    (105, 'David',   60,  4800.00),
    (106, 'Valli',   60,  4800.00);

CREATE TABLE departments (
    department_id INT,
    department_name TEXT,
    location_id INT
);

INSERT INTO departments VALUES
    (60, 'IT',         1400),
    (90, 'Executive',  1700);

​
Equality with ANY (equivalent to IN)

Find employees whose department_id matches any department located at location_id = 1700. 
SELECT name
FROM employees
WHERE department_id = ANY (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);
Returns:
name
Steven
Neena
Lex

​
Non-equality with ALL (equivalent to NOT IN)

Find employees whose department_id does not match any department located at location_id = 1700. 
SELECT name
FROM employees
WHERE department_id <> ALL (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);
Returns:
name
Alex
Bruce
David
Valli

​
Range comparison with ALL

Find employees whose salary is greater than the highest salary in department 60. Because > ALL is true only when the value exceeds every row, this is equivalent to comparing against the maximum. 
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 60
);
Returns:
namesalary
Steven24000.00
Neena17000.00
Lex17000.00

​
Range comparison with ANY

Find employees whose salary is greater than at least one salary in department 90. Because > ANY is true as soon as the value exceeds a single row, this is equivalent to comparing against the minimum. 
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 90
);
Returns:
namesalary
Steven24000.00

​
Correlated subquery

The subquery can reference columns from the outer query. The following query finds, for each employee, whether their salary is greater than or equal to every other salary in the same department — that is, the top earner(s) per department. 
SELECT name, department_id, salary
FROM employees e
WHERE salary >= ALL (
    SELECT salary
    FROM employees
    WHERE department_id = e.department_id
)
ORDER BY department_id, name;
Returns:
namedepartment_idsalary
Alex609000.00
Steven9024000.00

​
Empty subquery result

When the subquery returns no rows, ANY is FALSE and ALL is vacuously TRUE. 
SELECT 1 = ANY (SELECT department_id FROM departments WHERE location_id = 9999);
Returns: FALSE 
SELECT 1 <> ALL (SELECT department_id FROM departments WHERE location_id = 9999);
Returns: TRUE

​
NULL in the subquery result

If the subquery yields a NULL, the result follows three-valued logic. A definite match short-circuits the NULL: 
SELECT 1 = ANY (SELECT n FROM (VALUES (1), (NULL), (3)) v(n));
Returns: TRUE — the match on 1 is found before the NULL matters. 
SELECT 4 = ANY (SELECT n FROM (VALUES (1), (NULL), (3)) v(n));
Returns: NULL — no match is found, but a NULL prevents a definitive FALSE. 
SELECT 1 <> ALL (SELECT n FROM (VALUES (2), (NULL), (3)) v(n));
Returns: NULL — every non-NULL row differs from 1, but the NULL prevents a definitive TRUE.

​
See also