ARRAY_INTERSECT

Reference material for ARRAY_INTERSECT function

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(int)
[1]

Rows: 1Execution time: 4.91ms

Passing in one argument array is allowed:

SELECT ARRAY_INTERSECT(['red', 'maroon', 'crimson']) AS colors;

colors array(text)
[‘crimson’, ‘maroon’, ‘red’]

Rows: 1Execution time: 6.91ms

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(int)
[1, 3, 5]

Rows: 1Execution time: 6.10ms

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(int null)
[NULL, 9]

Rows: 1Execution time: 5.32ms

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(int)
[2, 1]

Rows: 1Execution time: 7.59ms

Arbitrarily nested arrays are also supported:

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

nested array(array(int) null)
[NULL, [1, 2]]

Rows: 1Execution time: 5.62ms