crate.io Logo
Toggle Doc Menu

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

  • =
  • >=
  • >
  • <=
  • <
  • <>

Feedback

How helpful was this page?
New issue Edit on GitHub View on GitHub