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
True

Rows: 1Execution time: 7.59ms

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
False

Rows: 1Execution time: 5.47ms

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
False

Rows: 1Execution time: 5.71ms

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
NULL

Rows: 1Execution time: 5.55ms

ARRAY_UPPER

ARRAY_FLATTEN

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Examples

​Syntax

​Parameters

​Return Type

​Examples

Syntax

Parameters

Return Type

Examples