Return the set difference of two arrays.
ARRAY_EXCEPT(array1, array2)## Overview Returns a new array containing the distinct elements that appear in the first array but not in the second. ARRAY_EXCEPT is the array-level equivalent of the EXCEPT set operator and is the most direct way to compute a differential between two array-valued expressions. Typical uses include computing newly-appearing tags between two snapshots, filtering an allow-list down to entries not already blocked, and deriving new values observed per row versus a reference collection. ## Behavior - Produces an ARRAY<T> containing each element of array1 exactly once, in its order of first occurrence, provided the element is not present in array2. - Duplicates in array1 collapse to a single value in the result; the output is always distinct. - Element equality is structural and follows SQL comparison rules, including for nested STRUCT and ARRAY elements. - NULL is treated as a real value for the purpose of equality: if array2 contains a NULL, NULLs are removed from the result; if array2 has no NULL, NULLs from array1 are retained. - Element types must be compatible; the result type is the common super-type. ## Null and empty handling - If either input array is NULL, the function returns NULL. Use COALESCE(col, ARRAY()) for NULL-tolerant behaviour. - If array1 is empty, the result is an empty array. - If array2 is empty, the result is ARRAY_DISTINCT(array1). - NULL element handling: if NULL is present in both arrays, NULL is excluded from the result; if NULL is only in array1, it is included once. ## Compatibility - Matches the standard set-difference semantics used by SQL array/map dialects. Some engines leave ordering unspecified; this engine preserves first-occurrence ordering. - Equivalent to ARRAY_DISTINCT(FILTER(array1, x -> NOT ARRAY_CONTAINS(array2, x))), but more efficient because no lambda evaluation is required.
| Name | Type | Description |
|---|---|---|
array1 | Specifies the source array whose distinct elements are retained in the result when absent from array2. | |
array2 | Specifies the array whose distinct elements are excluded from array1. Must share an element type with array1. |
-- Basic set difference
SELECT ARRAY_EXCEPT(ARRAY[1, 2, 3, 4], ARRAY[2, 4]); -- [1, 3]-- No overlap leaves array1 unchanged (deduplicated)
SELECT ARRAY_EXCEPT(ARRAY[1, 2], ARRAY[3, 4]); -- [1, 2]-- Compute tags present on a session but not on its user's baseline tags
SELECT session_id, ARRAY_EXCEPT(session_tags, baseline_tags) AS new_tags
FROM analytics.events.user_sessions;-- Empty right array returns the distinct elements of the left
SELECT ARRAY_EXCEPT(ARRAY[1, 1, 2, 3], CAST(ARRAY() AS ARRAY<INT>)); -- [1, 2, 3]-- NULL handling: a NULL element in array2 suppresses NULLs from the result
SELECT ARRAY_EXCEPT(ARRAY[1, CAST(NULL AS INT), 2], ARRAY[CAST(NULL AS INT)]); -- [1, 2]-- Identify newly introduced events per user
SELECT user_id, ARRAY_EXCEPT(ARRAY_AGG(DISTINCT event_type), ARRAY['session_start', 'session_end']) AS app_events
FROM analytics.events.user_sessions
GROUP BY user_id;