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

array expression && array expression

Syntax:

array_expression && array_expression

The && operator returns true if the two arrays have at least one element in common. If one of the argument is NULL the result is NULL.

This operator is an alias to the array_overlap(anyarray, anyarray) function.