ANY/ALL (array)

• 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

​

Return type

​

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]);

​

Check whether a value equals every element

SELECT 1 = ALL(ARRAY[1, 1, 1]);

SELECT 1 = ALL(ARRAY[1, 2, 1]);

​

Use inequality operators

SELECT 5 <> ALL(ARRAY[1, 2, 3]);

SELECT 1 <> ANY(ARRAY[1, 2, 3]);

​

Use range comparisons

SELECT 100 > ALL(ARRAY[10, 20, 30]);

SELECT 2 < ANY(ARRAY[1, 5, 10]);

​

Empty arrays

SELECT 1 = ANY(ARRAY[]::INT[]);

SELECT 1 = ALL(ARRAY[]::INT[]);

​

NULL values in the array

SELECT 1 = ANY(ARRAY[1, NULL, 3]);

SELECT 4 = ANY(ARRAY[1, NULL, 3]);

​

Use with table columns

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;