ARRAYS_OVERLAP
Returns whether all input arrays have at least one common, non-NULL element.
Note that if the input arrays have only a NULL element in common, ARRAYS_OVERLAP returns FALSE.
Returns NULL if any of the inputs is NULL.
Syntax
ARRAYS_OVERLAP(<array_1>, <array_2>, [, ...n])
Parameters
| Parameter | Description | Supported input types |
|---|---|---|
<array_1>, <array_2> [, ...n] | Two or more arrays to be tested for common elements. | ARRAY |
Return Type
BOOLEAN
Examples
The following example returns TRUE because all input arrays contain the element 2.
SELECT ARRAYS_OVERLAP(ARRAY[1, 2], ARRAY[2, 4], ARRAY[2, 6]) AS have_overlap
| have_overlap (BOOLEAN) |
|---|
| t |
The following example returns FALSE because no element appears in all input arrays.
SELECT ARRAYS_OVERLAP(ARRAY[1, 2], ARRAY[2, 4], ARRAY[1, 6]) AS have_overlap
| have_overlap (BOOLEAN) |
|---|
| f |
The following example returns FALSE because no non-NULL element appears in all input arrays.
SELECT ARRAYS_OVERLAP(ARRAY[NULL], ARRAY[NULL]) AS have_overlap
| have_overlap (BOOLEAN) |
|---|
| f |
The following example returns NULL because one of the inputs is NULL.
SELECT ARRAYS_OVERLAP(NULL, ARRAY[1, 2]) AS have_overlap
| have_overlap (BOOLEAN) |
|---|
| NULL |