# Array comparisons¶

An array comparison operator test the relationship between a value and an array and return `true`, `false`, or `NULL`.

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