Array comparisons¶
This section contains several constructs that can be used to make comparisons
between a list of values. Comparison operations result in a boolean value
(true
/false
) or null
.
These Comparison operators are supported for doing array comparisons.
For additional examples of row comparisons, see Subquery expressions.
Table of contents
IN (value [, ...])
¶
Syntax:
expression IN (value [, ...])
The binary operator IN
allows you to verify the membership of the left-hand
operand in the right-hand parenthesized list of scalar expressions.
Returns true
if any of the right-hand expression is found in the result of
the left-hand expression. It returns false
otherwise.
Here’s an example:
cr> select 1 in (1,2,3) AS a, 4 in (1,2,3) AS b;
+------+-------+
| a | b |
+------+-------+
| TRUE | FALSE |
+------+-------+
SELECT 1 row in set (... sec)
The result of the IN
construct yields null
if:
- The left-hand expression evaluates to
null
, and - There are no equal right-hand values and at least one right-hand value yields
null
ANY/SOME (array expression)
¶
Syntax:
expression operator ANY | SOME (array expression)
The ANY
construct returns true
if the defined comparison is true
for any of the values on the right-hand side array expression. It returns
false
if the values in the array expression do not match with the provided
comparison.
For example:
cr> select 1 = any ([1,2,3]) AS a, 4 = any ([1,2,3]) AS b;
+------+-------+
| a | b |
+------+-------+
| TRUE | FALSE |
+------+-------+
SELECT 1 row in set (... sec)
The result of the ANY
construct yields null
if:
- Either the expression or the array is
null
, and - No
true
comparison is obtained and any element of the array isnull
Note
The following is not supported by the ANY
operator:
is null
andis not null
asoperator
- Arrays of type
object
- Objects as
expressions
Tip
When using NOT <value> = ANY(<array_col>)
the performance of the query
could be quite bad, because special handling is required to implement the
3-valued logic. To achieve better performance, consider using the
ignore3vl function.
ALL (array expression)
¶
Syntax:
value operator ALL (array)
The left-hand expression is evaluated and compared against each element of the
right-hand array using the supplied operator. The result of ALL
is true
if all comparisons yield true
. The result is false
if the comparison of
at least one element does not match.
The result is NULL
if either the value or the array is NULL
or if no
comparison is false
and at least one comparison returns NULL
.
cr> SELECT 1 <> ALL(ARRAY[2, 3, 4]) AS x;
+------+
| x |
+------+
| TRUE |
+------+
SELECT 1 row in set (... sec)
Supported operators are:
=
>=
>
<=
<
<>