- Categories:
Semi-structured and Structured Data Functions (Array/Object)
ARRAYS_OVERLAP¶
Compares whether two arrays have at least one element in common. Returns TRUE if there is at least one element in common; otherwise returns FALSE. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality.
- See also:
Syntax¶
ARRAYS_OVERLAP( <array1> , <array2> )
Usage Notes¶
When you compare objects, the objects must be identical to return TRUE. For details, see Examples (in this topic).
If you are passing in structured ARRAYs, the ARRAY in the second argument must be comparable to the ARRAY in the first argument.
Examples¶
Here are some examples:
SELECT ARRAYS_OVERLAP(array_construct('hello', 'aloha'), array_construct('hello', 'hi', 'hey')) AS Overlap; +---------+ | OVERLAP | |---------| | True | +---------+ SELECT ARRAYS_OVERLAP(array_construct('hello', 'aloha'), array_construct('hola', 'bonjour', 'ciao')) AS Overlap; +---------+ | OVERLAP | |---------| | False | +---------+ SELECT ARRAYS_OVERLAP(array_construct(object_construct('a',1,'b',2), 1, 2), array_construct(object_construct('b',2,'c',3), 3, 4)) AS Overlap; +---------+ | OVERLAP | |---------| | False | +---------+ SELECT ARRAYS_OVERLAP(array_construct(object_construct('a',1,'b',2), 1, 2), array_construct(object_construct('a',1,'b',2), 3, 4)) AS Overlap; +---------+ | OVERLAP | |---------| | True | +---------+
The following example shows that NULL values are considered equal to other NULL values. If each array contains a NULL value, then the arrays overlap, even if no other (non-NULL) values overlap:
SELECT ARRAYS_OVERLAP(ARRAY_CONSTRUCT(1, 2, NULL), ARRAY_CONSTRUCT(3, NULL, 5)) AS Overlap; +---------+ | OVERLAP | |---------| | True | +---------+