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

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:

SELECT ARRAY_INTERSECT([ 1, 2, 3 ], [ 0, 1 ], [ 1, 5 ]) as result;

result (ARRAY(INTEGER))
{1}

Passing in one argument array is allowed:

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:

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:

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:

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:

SELECT ARRAY_INTERSECT([ [1], [2], NULL, [1,2] ], [ [1,2], NULL ]) as nested;

nested (ARRAY(ARRAY(INTEGER)))
{NULL,{1,2}}