ARRAYS_OVERLAP

​

Syntax

​

Scalar

arrays_overlap(left, right)

​

Arguments

• T is the array element type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY or STRUCT.

​

Returns

• Type: BOOLEAN
• Possible values: TRUE/FALSE.
• NULL behavior: Returns NULL if either array is NULL. Two NULL elements count as a match.

​

Properties

​

Usage notes

• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.

​

Examples

​

Typical behavior

SELECT arrays_overlap(ARRAY[1, 2], ARRAY[2, 3]) AS "value";

​

NULL elements

SELECT arrays_overlap(ARRAY[CAST(NULL AS INT)],ARRAY[CAST(NULL AS INT)]) AS both_null, arrays_overlap(ARRAY[1,CAST(NULL AS INT)],ARRAY[2]) AS disjoint_with_null;

​

Limitations

• Returns TRUE when both arrays contain NULL. Returns FALSE when they have no matching elements, even if one contains NULL. These NULL rules differ from Spark. Use ARRAYS_OVERLAP; ARRAY_HAS_ANY is not an established alias.
• MAP elements, including MAP values nested inside compared elements, are outside the supported element types.