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

# ARRAY_INTERSECTION

> Returns distinct elements that occur in both arrays.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Scalar function</span></div>

**Aliases:** [`array_intersect`](/query/sql/functions/array/array_intersect)

Returns NULL when either array is NULL. A NULL element can participate in the intersection.

## Syntax

### Scalar

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
array_intersection(left, right)
```

## Arguments

Type notation used below:

* T is the array element type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY or STRUCT.

| Name | Type | Required | Description |
| - | - | - | - |
| left | ARRAY\<T> | Yes | The first array. Nested elements/fields must satisfy the same element-type restrictions. |
| right | ARRAY\<T> | Yes | The second array. Nested elements/fields must satisfy the same element-type restrictions. |

## Returns

* Type: ARRAY\<T>
* Possible values: The distinct intersection.
* NULL behavior: Returns NULL when either array is NULL. A NULL element can participate in the intersection.
* Shape: At most the distinct count of either operand.
* Ordering: Values follow their order in the longer input array, or the left array when lengths match. They are not sorted.

## Properties

| Property | Value |
| - | - |
| Deterministic | Yes |
| Supports window usage | No |
| Exact result | Yes |

## Usage notes

* NULL elements can match. For floating-point elements, positive and negative zero are different, and NaN values with different binary representations can differ. The result removes duplicates and follows the order of the longer input array, or the left array when lengths match.
* INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.

## Examples

### Typical behavior

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT array_intersection(ARRAY[1, 2, 2, 3], ARRAY[2, 4]) AS "value";
```

| value |
| - |
| \[2] |

### Intersect string arrays

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT array_intersection(ARRAY('red', 'blue', 'red'), ARRAY('red', 'green')) AS shared_colors;
```

| shared\_colors |
| - |
| \["red"] |

## Limitations

* Elements must have types that can be compared, including any nested values.
* MAP elements, including MAP values nested inside compared elements, are outside the supported element types.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.