ARRAY_INTERSECTION

​

Syntax

​

Scalar

array_intersection(left, right)

​

Arguments

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

​

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

​

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

SELECT array_intersection(ARRAY[1, 2, 2, 3], ARRAY[2, 4]) AS "value";

​

Intersect string arrays

SELECT array_intersection(ARRAY('red', 'blue', 'red'), ARRAY('red', 'green')) AS shared_colors;

​

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.