ARRAYS_OVERLAP

Test whether two arrays share at least one element.

Category: collectionReturns: BOOLEANDialect: Standard

Syntax

ARRAYS_OVERLAP(array1, array2)

Description

## Overview Returns true if the two input arrays share at least one element, false if they are disjoint, and NULL when either operand is NULL. ARRAYS_OVERLAP is the boolean counterpart of ARRAY_INTERSECT and is the fastest way to gate rows on whether two array-valued expressions intersect. This function is the standard-dialect name. See ARRAY_OVERLAP for the standard-compatible synonym with identical semantics. Typical uses include WHERE-clause filters that check overlap between a per-row array and a reference set, HAVING clauses that gate groups by whether their aggregated array intersects a watchlist, and join predicates that match on any-of-many semantics. ## Behavior - Returns BOOLEAN (true, false, or NULL). - Uses structural equality for element comparison, including STRUCT, MAP, and nested ARRAY elements. - Short-circuits on the first match; it does not scan further once overlap is confirmed. - Works on any comparable element type. - Element ordering and duplicates do not matter; only set membership is checked. ## Null and empty handling - NULL on either side returns NULL. - Empty array on either side returns false (an empty set cannot overlap). - If both arrays contain NULL, ARRAYS_OVERLAP returns true because NULL matches NULL in this function. - NULL elements on only one side do not cause three-valued output; the function is definite as long as both inputs are non-NULL. ## Compatibility - Matches the standard-dialect ARRAYS_OVERLAP convention. The standard-compatible && operator and ARRAY_OVERLAP are synonymous in this engine. - Equivalent to ARRAY_SIZE(ARRAY_INTERSECT(array1, array2)) > 0 but avoids materialising the intersection.

Parameters

Name	Type	Description
`array1`		Specifies the first array to compare.
`array2`		Specifies the second array. Must share an element type with array1.

Examples

-- Overlapping arrays
SELECT ARRAYS_OVERLAP(ARRAY[1, 2, 3], ARRAY[3, 4, 5]);  -- true

-- Disjoint arrays
SELECT ARRAYS_OVERLAP(ARRAY[1, 2], ARRAY[3, 4]);  -- false

-- String-array overlap
SELECT ARRAYS_OVERLAP(ARRAY['x', 'y'], ARRAY['y', 'z']);  -- true

-- Empty side yields false
SELECT ARRAYS_OVERLAP(ARRAY[1, 2], CAST(ARRAY() AS ARRAY<INT>));  -- false

-- Filter sessions whose tags intersect a VIP set
SELECT session_id
FROM analytics.events.user_sessions
WHERE ARRAYS_OVERLAP(tags, ARRAY['vip', 'beta']);

-- NULL inputs yield NULL
SELECT ARRAYS_OVERLAP(CAST(NULL AS ARRAY<INT>), ARRAY[1, 2]);  -- NULL

Pitfalls

NULL on either side returns NULL, not false. Filters such as WHERE ARRAYS_OVERLAP(a, b) silently drop rows with NULL arrays unless a COALESCE or IS NOT NULL guard is added.
Empty arrays always return false. Use ARRAY_SIZE to distinguish empty inputs from disjoint inputs when needed.
NULL elements match each other in this function, unlike three-valued ARRAY_CONTAINS semantics. Plan predicates accordingly when both sides may contain NULL.
Complexity is bounded by the smaller of the two arrays when a hash set can be built. For very large per-row arrays, consider precomputing membership bitmaps.