> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference material for ARRAY_INTERSECT function

# ARRAY_INTERSECT

Finds the intersection of the provided argument arrays. Used for finding the common elements between arrays.

Returns an array containing all the elements that are present in every argument array. It uses set semantics, which means the result cannot contain multiple copies of the same value. The order of elements in the result may be different than in the original arrays; use [ARRAY\_SORT](/reference-sql/functions-reference/array/array-sort) to stipulate a specific order on the results.

This function is NULL-safe, because it treats `NULL` values *within* arrays as known values, as shown in the following examples.

If any of the input argument arrays are themselves `NULL`, the function returns `NULL`.

## Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ARRAY_INTERSECT(<array> [, ...n])
```

## Parameters

| Parameter          | Description                                               | Supported input types |
| :----------------- | :-------------------------------------------------------- | :-------------------- |
| `<array> [, ...n]` | The argument arrays whose intersection is to be computed. | `ARRAY`               |

## Return Type

`ARRAY` of the common type of all input arrays.

The common type is the supertype of the provided array types. For example, the supertype between `Array(Int)` and `Array(BigInt)` is `Array(BigInt)`.

## Examples

In the following example, the only element shared between all three arrays is the `1`:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT ARRAY_INTERSECT([ 1, 2, 3 ], [ 0, 1 ], [ 1, 5 ]) as result;
```

| result (ARRAY(INTEGER)) |
| :---------------------- |
| `{1}`                   |

Passing in one argument array is allowed:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT ARRAY_INTERSECT([ 'red', 'maroon', 'crimson' ]) as colors;
```

| colors (ARRAY(TEXT))   |
| :--------------------- |
| `{crimson,maroon,red}` |

In the following example, `ARRAY_SORT` is used to ensure the results are in ascending order:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT ARRAY_SORT(
        ARRAY_INTERSECT([ 5, 4, 3, 2, 1 ],[ 5, 3, 1 ])
    ) as sorted;
```

| sorted (ARRAY(INTEGER)) |
| :---------------------- |
| `{1,3,5}`               |

`NULL` can appear in the intersection, only if it appears in all the argument arrays:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT ARRAY_INTERSECT([ 1, 9, NULL ],[ 8, 9, NULL ], [4, 9, NULL]) as contains_null;
```

| contains\_null (ARRAY(INTEGER)) |
| :------------------------------ |
| `{NULL,9}`                      |

The result does not contain duplicates, even if the same value appears multiple times in all argument arrays:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT ARRAY_INTERSECT([ 1, 2, 2, 8 ],[ 1, 2, 2, 2, 6 ]) as unique;
```

| unique (ARRAY(INTEGER)) |
| :---------------------- |
| `{2,1}`                 |

Arbitrarily nested arrays are also supported:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT ARRAY_INTERSECT([ [1], [2], NULL, [1,2] ], [ [1,2], NULL ]) as nested;
```

| nested (ARRAY(ARRAY(INTEGER))) |
| :----------------------------- |
| `{NULL,{1,2}}`                 |