ARRAY_EXCEPT - MaxCompute - Alibaba Cloud Documentation Center

Finds the elements that exist in Array a but do not exist in Array b and returns the elements as a new array without duplicates.

Syntax

array<T> array_except(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:

The returned array has no duplicate elements, and the elements are sorted in the same order as Array a.
If an element in an array is null, the null element is involved in the operation.
If one of the input arrays is empty, a new array is returned based on the non-empty array without duplicates.
If the two input arrays are empty, an empty array is returned.

Examples

Example 1: Find the elements that exist in array(1, 1, 3, 3, 5, 5) but do not exist in array(1, 1, 2, 2, 3, 3) and return the elements as a new array without duplicates. Sample statement:
```
-- The return value is [5]. 
select array_except(array(1, 1, 3, 3, 5, 5), array(1, 1, 2, 2, 3, 3));
```
Example 2: Find the elements that exist in array(1, 1, 3, 3, 5, 5, null, null) but do not exist in array(1, 1, 2, 2, 3, 3) and return the elements as a new array without duplicates. Sample statement:
```
-- The return value is [5,null]. 
select array_except(array(1, 1, 3, 3, 5, 5, null, null), array(1, 1, 2, 2, 3, 3));
```
Example 3: Find the elements that exist in an array but do not exist in another array and return the elements as a new array without duplicates. One of the input arrays is empty. Sample statement:
```
-- The return value is [2,1]. 
select array_except(array(2, 1, 1, 2), cast(array() as array<int>)); 
```
Example 4: Find the elements that exist in an array but do not exist in another array and return the elements as a new array without duplicates. The two input arrays are empty. Sample statement:
```
-- The return value is []. 
select array_except(cast(array() as array<int>), cast(array() as array<int>));
```

Related functions

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