Array comparisons

An array comparison operator tests the relationship between two arrays and returns a corresponding value of true, false, or NULL.

See also

Subquery expressions

Table of contents

IN (value [, ...])

Syntax:

  1. expression IN (value [, ...])

Here’s an example:

  1. cr> select 1 in (1,2,3) AS a, 4 in (1,2,3) AS b;
  2. +------+-------+
  3. | a    | b     |
  4. +------+-------+
  5. | TRUE | FALSE |
  6. +------+-------+
  7. SELECT 1 row in set (... sec)

The IN operator returns true if any of the right-hand values matches the left-hand operand. Otherwise, it returns false (including the case where 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

ANY/SOME (array expression)

Syntax:

  1. expression comparison ANY | SOME (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:

  1. cr> select 1 = any ([1,2,3]) AS a, 4 = any ([1,2,3]) AS b;
  2. +------+-------+
  3. | a    | b     |
  4. +------+-------+
  5. | TRUE | FALSE |
  6. +------+-------+
  7. 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.

The operator returns false if the comparison returns false for all right-hand values or 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#Comparisonswith_NULL_and_the_three-valued_logic(3VL)). To achieve better performance, consider using the ignore3vl function.

ALL (array_expression)

Syntax:

  1. 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:

  1. cr> SELECT 1 <> ALL(ARRAY[2, 3, 4]) AS x;
  2. +------+
  3. | x    |
  4. +------+
  5. | TRUE |
  6. +------+
  7. 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