Array comparisons

An array comparison operator test the relationship between a value and an array and return true, false, or NULL.

See also

Subquery expressions

Table of contents

IN (value [, ...])

Syntax:

expression IN (value [, ...])

The IN operator returns true if the left-hand matches at least one value contained within the right-hand side.

The operator returns NULL if:

  • The left-hand expression evaluates to NULL

  • There are no matching right-hand values and at least one right-hand value is NULL

Here’s an example:

cr> SELECT
...   1 in (1, 2, 3) AS a,
...   4 in (1, 2, 3) AS b,
...   5 in (1, 2, null) as c;
+------+-------+------+
| a    | b     | c    |
+------+-------+------+
| TRUE | FALSE | NULL |
+------+-------+------+
SELECT 1 row in set (... sec)

ANY/SOME (array expression)

Syntax:

expression <comparison> ANY | SOME (array_expression)

Here, <comparison> can be any basic comparison operator.

An 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 ANY operator returns true if the defined comparison is true for any of the values in the right-hand array expression.

If the right side is a multi-dimension array it is automatically unnested to the required dimension.

An example:

cr> SELECT
...   4 = ANY ([[1, 2], [3, 4]]) as a,
...   5 = ANY ([[1, 2], [3, 4]]) as b,
...   [1, 2] = ANY ([[1,2], [3, 4]]) as c,
...   [1, 3] = ANY ([[1,2], [3, 4]]) as d;
+------+-------+------+-------+
| a    | b     | c    | d     |
+------+-------+------+-------+
| TRUE | FALSE | TRUE | FALSE |
+------+-------+------+-------+
SELECT 1 row in set (... sec)

The operator returns false if the comparison returns false for all right-hand values or if there are no right-hand values.

The operator returns NULL if:

  • The left-hand expression evaluates to NULL

  • There are no matching right-hand values and at least one right-hand value is NULL

Tip

When doing NOT <value> = ANY(<array_col>), query performance may be degraded 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 comparison ALL (array_expression)

Here, comparison can be any basic comparison operator. Objects and arrays of objects are not supported for either operand.

Here’s an example:

cr> SELECT 1 <> ALL(ARRAY[2, 3, 4]) AS x;
+------+
| x    |
+------+
| TRUE |
+------+
SELECT 1 row in set (... sec)

The ALL operator returns true if the defined comparison is true for all values in the right-hand array expression.

The operator returns false if the comparison returns false for all right-hand values.

The operator returns NULL if:

  • The left-hand expression evaluates to NULL

  • No comparison returns false and at least one right-hand value is NULL