# Array comparisons¶

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

Table of contents

## `IN (value [, ...])`¶

Syntax:

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

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

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

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

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

## Feedback

How helpful was this page?