Returns a new array containing elements that exist in array a but not in array b, with duplicates removed. null elements are treated as known values and are included in the comparison.
Syntax
array<T> array_except(array<T> <a>, array<T> <b>)Parameters
| Parameter | Required | Description |
|---|---|---|
a | Yes | Source array. T is the element data type, which can be any supported type. |
b | Yes | Array of elements to exclude. Must have the same element data type as a. |
Return value
Returns an ARRAY value. The return value follows these rules:
Elements appear in the same order as in array
a, with duplicates removed.null elements are included in the comparison — a null in
athat is not inbappears in the result.If one array is empty, the function returns the non-empty array with duplicates removed.
If both arrays are empty, the function returns an empty array.
Examples
Example 1: Basic set difference
Find elements in array(1, 1, 3, 3, 5, 5) that are not in array(1, 1, 2, 2, 3, 3).
-- Returns [5]: 5 is in a but not in b; duplicate 5s in a are collapsed to one.
select array_except(array(1, 1, 3, 3, 5, 5), array(1, 1, 2, 2, 3, 3));Example 2: null elements are not ignored
Find elements in array(1, 1, 3, 3, 5, 5, null, null) that are not in array(1, 1, 2, 2, 3, 3).
-- Returns [5,null]: null is in a but not in b, so it is included in the result.
select array_except(array(1, 1, 3, 3, 5, 5, null, null), array(1, 1, 2, 2, 3, 3));Example 3: One array is empty
Find elements in array(2, 1, 1, 2) that are not in an empty array.
-- Returns [2,1]: b is empty, so all distinct elements from a are returned in their original order.
select array_except(array(2, 1, 1, 2), cast(array() as array<int>));Example 4: Both arrays are empty
-- Returns []: both arrays are empty, so the result is an empty array.
select array_except(cast(array() as array<int>), cast(array() as array<int>));Related functions
ARRAY_EXCEPT is a complex type function. For more information about functions that process ARRAY, MAP, STRUCT, and JSON data types, see Complex type functions.