ARRAY_OVERLAP

Test whether two arrays share at least one element.

Category: collectionReturns: BOOLEANDialect: PostgreSql

Syntax

ARRAY_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. ARRAY_OVERLAP is the boolean counterpart of ARRAY_INTERSECT and is the preferred predicate for high-selectivity membership tests between two array-valued expressions. This function is the standard-compatible name. See ARRAYS_OVERLAP for the standard-dialect synonym, which has identical semantics. Typical uses include filtering rows whose tag column overlaps with a campaign tag set, joining fact and reference tables on multi-valued attributes, and short-circuiting set-intersection logic when only the boolean answer is needed. ## 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 and does not scan the remaining elements. - Works on any comparable element type. Type widening follows standard promotion rules. - Element ordering and duplicates do not matter; only 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, ARRAY_OVERLAP returns true because NULL matches NULL in this function (unlike three-valued ARRAY_CONTAINS semantics). - If one side has NULL elements and the other has no NULL, overlap depends only on the non-NULL elements; the presence of NULL does not force three-valued output here. ## Compatibility - Matches the standard SQL && array-overlap operator and the ARRAYS_OVERLAP function in standard-dialect array/map SQL. - 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 ARRAY_OVERLAP(ARRAY[1, 2, 3], ARRAY[3, 4, 5]);  -- true

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

-- Tag arrays
SELECT ARRAY_OVERLAP(ARRAY['a', 'b'], ARRAY['b', 'c']);  -- true

-- Any empty array returns false
SELECT ARRAY_OVERLAP(ARRAY[1, 2], CAST(ARRAY() AS ARRAY<INT>));  -- false

-- Filter sessions whose tags overlap with a campaign tag set
SELECT session_id
FROM analytics.events.user_sessions
WHERE ARRAY_OVERLAP(tags, ARRAY['promo_spring', 'promo_summer']);

-- NULL-array inputs return NULL
SELECT ARRAY_OVERLAP(CAST(NULL AS ARRAY<INT>), ARRAY[1, 2]);  -- NULL

Pitfalls

NULL on either side returns NULL, not false. COALESCE both inputs to ARRAY() for a definite boolean result.
An empty array always produces false, even when the other side is also empty. Use ARRAY_SIZE if you need to distinguish the empty case explicitly.
NULL elements match each other in this function, which differs from ARRAY_CONTAINS three-valued logic. Plan predicates accordingly.
ARRAY_OVERLAP is O(m + n) worst case but short-circuits early when a match is found. For very large arrays, consider pre-deduplication or hash-based set designs.