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 verfiy 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), 4 in (1,2,3);
+------------------+------------------+
| (1 IN (1, 2, 3)) | (4 IN (1, 2, 3)) |
+------------------+------------------+
| 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]), 4 = any ([1,2,3]);
+------------------+------------------+
| 1 = ANY([1,2,3]) | 4 = ANY([1,2,3]) |
+------------------+------------------+
| 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 is null

Note

The following is not supported by the ANY operator:

  • is null and is not null as operator
  • 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#Comparisonswith_NULL_and_the_three-valued_logic(3VL)). To achieve better performance, consider using the ignore3vl function.