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

## `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:

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