Introduction to the ARRAY_INTERSECT function of MaxCompute - MaxCompute

Calculates the intersection of two arrays and returns a new array that contains the values that exist in both arrays. This topic describes the syntax, parameters, and examples of the ARRAY_INTERSECT function.

Syntax

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

Parameters

a and b: required. These parameters specify arrays. T in array<T> specifies the data type of the elements in the arrays. The elements can be of any data type. The elements in Array a and the elements in Array b must be of the same data type.

Return value

A value of the ARRAY type is returned. The return value varies based on the following rules:

If an element in an array is null, the null element is involved in the operation.
The returned array has no duplicate elements, and the elements are sorted in the same order as Array a.
If Array a or Array b is null, null is returned.

Examples

Example 1: Calculate the intersection of array(1, 2, 3) and array(1, 3, 5), and remove duplicate elements. Sample statement:
```
-- The return value is [1,3]. 
select array_intersect(array(1, 2, 3), array(1, 3, 5));
```
Example 2: Calculate the intersection of array(10, 20, 20, 30, 30, null, null) and array(30, 30, 20, 20, 40, null, null), and remove duplicate elements. Sample statement:
```
-- The return value is [20,30,null]. 
select array_intersect(array(10, 20, 20, 30, 30, null, null), array(30, 30, 20, 20, 40, null, null)); 
```

Related functions

ARRAY_INTERSECT is a complex type function. For more information about the functions that are used to process data of complex data types, such as ARRAY, MAP, STRUCT, and JSON, see Complex type functions.