MaxCompute:ARRAY_INTERSECT

Syntax

array<T> array_intersect(array<T> <a>, array<T> <b>)

T is the data type of the array elements. Both arrays must contain elements of the same data type.

Parameters

Return value

Returns an ARRAY value. The result follows these rules:

• Deduplication: The returned array contains no duplicate elements. Even if an element appears multiple times in both a and b, it appears only once in the result.

• Order: Elements are returned in the same order as they appear in a.

• Null handling: null elements participate in the comparison. If null appears in both arrays, null is included in the result.

• Null input: If a or b is null, null is returned.

Usage notes

Deduplication applies regardless of input duplicates. If both a and b contain multiple copies of the same value, the result contains exactly one copy — not the minimum of the two counts. For example, if a contains [20, 20] and b contains [20, 20, 20], the result contains [20].

This behavior differs from multi-set (bag) semantics used by some other systems. ARRAY_INTERSECT always returns a deduplicated result.

Examples

Example 1: Basic intersection

-- Returns [1,3]
select array_intersect(array(1, 2, 3), array(1, 3, 5));

Elements 1 and 3 appear in both arrays. Element 2 is only in the first array and element 5 is only in the second, so neither appears in the result.

Example 2: Intersection with duplicates and null values

-- Returns [20,30,null]
select array_intersect(array(10, 20, 20, 30, 30, null, null), array(30, 30, 20, 20, 40, null, null));

Although 20, 30, and null appear multiple times in both arrays, each appears only once in the result. Element 10 is absent from b and element 40 is absent from a, so neither is included.

Related functions

ARRAY_INTERSECT is a complex type function. For other functions that work with ARRAY, MAP, STRUCT, and JSON data, see Complex type functions.