Categories:

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

Syntax

ARRAYS_OVERLAP( <array1> , <array2> )

Usage Notes

  • When comparing objects, the objects must be identical to return TRUE. For details, see Examples (in this topic).

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    |
+---------+