Skip to main content
Quantified comparison operators compare a scalar value against the elements of an array using a <comparison> operator combined with an ANY or ALL quantifier.
  • ANY returns TRUE if the comparison is true for at least one element in the array.
  • ALL returns TRUE if the comparison is true for every element in the array.

​
Syntax

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

​
Parameters

ParameterDescriptionSupported input types
<value>A scalar value to compare against each element of the array.Any comparable type
<comparison>A comparison operator: =, <>, !=, <, >, <=, or >=.See Comparison operators
<array>The array whose elements are compared against <value>.ARRAY with elements of a comparable type
The type of <value> and the element type of <array> must be comparable.

​
Return type

BOOLEAN

​
NULL handling

  • If <array> is NULL, the result is NULL.
  • If <array> is empty, ANY returns FALSE and ALL returns TRUE.
  • For ANY: returns TRUE as soon as a matching element is found, even if other elements are NULL. If no element matches and at least one element is NULL, the result is NULL.
  • For ALL: returns FALSE as soon as a non-matching element is found, even if other elements are NULL. If every non-NULL element matches and at least one element is NULL, the result is NULL.

​
Examples

​
Check whether a value exists in an array

SELECT 2 = ANY(ARRAY[1, 2, 3]);
Returns: true

​
Check whether a value equals every element

SELECT 1 = ALL(ARRAY[1, 1, 1]);
Returns: true 
SELECT 1 = ALL(ARRAY[1, 2, 1]);
Returns: false

​
Use inequality operators

SELECT 5 <> ALL(ARRAY[1, 2, 3]);
Returns: true — 5 differs from every element. 
SELECT 1 <> ANY(ARRAY[1, 2, 3]);
Returns: true — 1 differs from at least one element.

​
Use range comparisons

SELECT 100 > ALL(ARRAY[10, 20, 30]);
Returns: true — 100 is greater than every element. 
SELECT 2 < ANY(ARRAY[1, 5, 10]);
Returns: true — 2 is less than at least one element.

​
Empty arrays

SELECT 1 = ANY(ARRAY[]::INT[]);
Returns: false — no elements to match against. 
SELECT 1 = ALL(ARRAY[]::INT[]);
Returns: true — the condition holds vacuously for an empty array.

​
NULL values in the array

SELECT 1 = ANY(ARRAY[1, NULL, 3]);
Returns: true — a match is found before the NULL matters. 
SELECT 4 = ANY(ARRAY[1, NULL, 3]);
Returns: NULL — no match is found, but a NULL element prevents a definitive false.

​
Use with table columns

The following example checks each row’s quantity against its own valid_quantities array. 
CREATE DIMENSION TABLE inventory (
    product_id INT,
    quantity INT,
    valid_quantities ARRAY(INT)
);

INSERT INTO inventory VALUES
    (1, 10, [10, 20, 30]),
    (2, 15, [10, 20, 30]),
    (3, 20, [10, 20, 30]);

SELECT
    product_id,
    quantity = ANY(valid_quantities) AS is_valid
FROM inventory;
Returns:
product_idis_valid
1true
2false
3true