Returns true if arrays a and b share at least one non-null element. If no shared non-null element exists but either array contains NULL, returns null because NULL is treated as an unknown value. Returns false only when both arrays are non-empty and share no elements (including no nulls).
Syntax
boolean arrays_overlap(array<T> <a>, array<T> <b>)Parameters
a and b: required. The arrays to compare. T in array<T> is the element data type. Both arrays must use the same element data type.
Supported element data types:
-
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
Returns BOOLEAN.
| Condition | Return value |
|---|---|
| At least one element appears in both arrays and is not null | true |
| No shared elements, at least one array contains null, and both arrays are non-empty | null |
| No shared elements, both arrays are non-empty and contain no null elements | false |
Examples
Example 1: Element 3 appears in both arrays — returns true.
-- Returns true
select arrays_overlap(array(1, 2, 3), array(3, 4, 5));Example 2: No shared elements, no nulls — returns false.
-- Returns false
select arrays_overlap(array(1, 2, 3), array(6, 4, 5));Example 3: No shared elements, but the second array contains NULL — returns null.
-- Returns null
select arrays_overlap(array(1, 2, 3), array(5, 4, null));The result is null because MaxCompute treats NULL as an unknown value. Since the second array may contain any undetermined value, the function cannot confirm that no overlap exists, so it returns null instead of false.
Related functions
ARRAYS_OVERLAP is a complex type function. For a full list of functions that process ARRAY, MAP, STRUCT, and JSON data types, see Complex type functions.