<comparison> operator combined with an ANY or ALL quantifier.
ANYreturnsTRUEif the comparison is true for at least one element in the array.ALLreturnsTRUEif the comparison is true for every element in the array.
Syntax
Parameters
The type of
<value> and the element type of <array> must be comparable.
Return type
BOOLEAN
NULL handling
- If
<array>isNULL, the result isNULL. - If
<array>is empty,ANYreturnsFALSEandALLreturnsTRUE. - For
ANY: returnsTRUEas soon as a matching element is found, even if other elements areNULL. If no element matches and at least one element isNULL, the result isNULL. - For
ALL: returnsFALSEas soon as a non-matching element is found, even if other elements areNULL. If every non-NULL element matches and at least one element isNULL, the result isNULL.
Examples
Check whether a value exists in an array
Rows: 1Execution time: 3.05ms
Check whether a value equals every element
Rows: 1Execution time: 3.23ms
Rows: 1Execution time: 3.24ms
Use inequality operators
Rows: 1Execution time: 3.07ms
5 differs from every element.
Rows: 1Execution time: 3.12ms
1 differs from at least one element.
Use range comparisons
Rows: 1Execution time: 2.80ms
100 is greater than every element.
Rows: 1Execution time: 3.09ms
2 is less than at least one element.
Empty arrays
Rows: 1Execution time: 2.85ms
Rows: 1Execution time: 2.44ms
NULL values in the array
Rows: 1Execution time: 3.48ms
NULL matters.
Rows: 1Execution time: 1ms
NULL element prevents a definitive false.
Use with table columns
The following example checks each row’squantity against its own valid_quantities array.