ARRAYS_OVERLAP - MaxCompute - Alibaba Cloud Documentation Center

Checks whether Array a and Array b contain the same element.

Syntax

boolean arrays_overlap(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.

The following data types are supported:

TINYINT, SMALLINT, INT, and BIGINT
FLOAT and DOUBLE
BOOLEAN
DECIMAL and DECIMALVAL
DATE, DATETIME, TIMESTAMP, IntervalDayTime, and IntervalYearMonth
STRING, BINARY, VARCHAR, and CHAR
ARRAY, STRUCT, and MAP

Return value

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

If Array a contains at least one element that is in Array b and is not null, true is returned.
If Array a and Array b do not contain the same element, both of the arrays are not empty, and one or both of the arrays contain a null element, null is returned.
If Array a and Array b do not contain the same element, and both of the arrays are not empty and do not contain a null element, false is returned.

Examples

Example 1: Check whether array(1, 2, 3) and array(3, 4, 5) contain the same element. Sample statement:
```
-- The return value is true. 
select arrays_overlap(array(1, 2, 3), array(3, 4, 5));
```
Example 2: Check whether array(1, 2, 3) and array(6, 4, 5) contain the same element. Sample statement:
```
-- The return value is false. 
select arrays_overlap(array(1, 2, 3), array(6, 4, 5));
```

Example 3: Check whether one of the arrays contains the null element. Sample statement:

-- The return value is null. 
select arrays_overlap(array(1, 2, 3), array(5, 4, null));

Related functions

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