ARRAY_INTERSECT

Return the distinct elements present in both arrays.

Category: collectionReturns: ARRAY<T>Dialect: Standard

Syntax

ARRAY_INTERSECT(array1, array2)

Description

## Overview Returns a new array containing each element that appears in both input arrays, without duplicates. ARRAY_INTERSECT is the array-level counterpart of the SQL INTERSECT set operator and is the most direct way to compute the overlap between two array-valued columns or literals. Typical uses include finding shared tags between a row and a reference set, identifying common event types across two time windows, and implementing tag-based allow-list enforcement in a single expression. ## Behavior - Produces an ARRAY<T> containing each element exactly once. - Preserves the order of first occurrence from array1. Elements that appear only in array2 are not reordered into the result. - Equality is structural and follows SQL comparison rules for STRUCT, MAP, and nested ARRAY elements. - NULL is treated as a concrete value for matching: if both arrays contain NULL, the result contains a single NULL element. - Duplicates in either input collapse; the result is always distinct. - Element types must be compatible; the result uses the common super-type. ## Null and empty handling - NULL input on either side returns NULL. - Empty input on either side returns an empty array of the common element type. - Matching NULL elements: if both arrays contain NULL, the result contains one NULL; otherwise NULL is not included. ## Compatibility - Matches the standard set-intersection semantics for array/map SQL dialects. - Equivalent to ARRAY_DISTINCT(FILTER(array1, x -> ARRAY_CONTAINS(array2, x))), but avoids lambda evaluation overhead.

Parameters

Name	Type	Description
`array1`		Specifies the first array. The result uses its order-of-first-occurrence for the intersected elements.
`array2`		Specifies the second array. Must share an element type with array1.

Examples

-- Basic intersection
SELECT ARRAY_INTERSECT(ARRAY[1, 2, 3], ARRAY[2, 3, 4]);  -- [2, 3]

-- Common tags between a session and a reference set
SELECT session_id, ARRAY_INTERSECT(tags, ARRAY['vip', 'beta', 'churn_risk']) AS matched_tags
FROM analytics.events.user_sessions;

-- No common elements returns an empty array
SELECT ARRAY_INTERSECT(ARRAY[1, 2], ARRAY[3, 4]);  -- []

-- Duplicates in either side collapse to one copy
SELECT ARRAY_INTERSECT(ARRAY[1, 1, 2, 3], ARRAY[1, 3, 3]);  -- [1, 3]

-- NULL element in both arrays: NULL is considered a match
SELECT ARRAY_INTERSECT(ARRAY[1, CAST(NULL AS INT), 2], ARRAY[CAST(NULL AS INT), 3]);  -- [NULL]

-- Find users whose session events overlap with a critical funnel set
SELECT user_id,
       ARRAY_INTERSECT(ARRAY_AGG(event_type), ARRAY['checkout_start', 'purchase']) AS funnel_hits
FROM analytics.events.user_sessions
GROUP BY user_id;

Pitfalls

ARRAY_INTERSECT deduplicates the result. If you need multiset semantics (each element repeated min(count_a, count_b) times), roll your own with EXPLODE and a GROUP BY.
A NULL input on either side returns NULL, not an empty array. COALESCE both inputs to ARRAY() when you need set arithmetic to degrade gracefully.
NULL element matching treats NULL as equal to NULL for this function, which differs from ARRAY_CONTAINS three-valued semantics. Be explicit about which behaviour you want.
Element types must be compatible. Different STRUCT shapes or mismatched array nesting levels raise a type error rather than producing an empty result.