ARRAY_UNION

Return the distinct union of two arrays.

Category: collectionReturns: ARRAY<T>Dialect: Standard

Syntax

ARRAY_UNION(array1, array2)

Description

## Overview Returns a new array containing the distinct union of the two input arrays, preserving the order of first occurrence across them. ARRAY_UNION is the array-level counterpart of the SQL UNION set operator and is the most direct way to merge two array-valued expressions while eliminating duplicates. Typical uses include combining per-row tag arrays with a canonical reference set, merging two event lists into a distinct master trail, and building the union of multiple array-valued attributes prior to downstream lookup or comparison. ## Behavior - Produces an ARRAY<T> containing each distinct element exactly once. - Ordering: elements from array1 appear first (in their first-occurrence order), followed by elements from array2 that were not already in array1 (also in first-occurrence order). - Equality is structural and uses SQL comparison rules. - NULL is treated as a concrete value: if either array contains NULL, a single NULL is included in the result. - Element types must be compatible; the result uses the common super-type. ## Null and empty handling - NULL on either side returns NULL. - Empty array on either side returns ARRAY_DISTINCT of the other side. - NULL elements in either operand collapse to a single NULL in the output. - Empty inputs on both sides return an empty array of the common element type. ## Compatibility - Matches the standard set-union semantics for array/map SQL dialects. Some dialects leave ordering unspecified; this engine uses first-occurrence ordering across the two operands. - Equivalent to ARRAY_DISTINCT(ARRAY_CAT(array1, array2)) but more efficient because the distinctness pass is done inline.

Parameters

Name	Type	Description
`array1`		Specifies the first array. Its distinct elements appear first in the result in their order of first occurrence.
`array2`		Specifies the second array. Must share an element type with array1. Elements not already present from array1 are appended in order of first occurrence.

Examples

-- Basic union
SELECT ARRAY_UNION(ARRAY[1, 2, 3], ARRAY[2, 3, 4]);  -- [1, 2, 3, 4]

-- Disjoint arrays concatenate into a distinct result
SELECT ARRAY_UNION(ARRAY[1, 2], ARRAY[3, 4]);  -- [1, 2, 3, 4]

-- Duplicates within arrays collapse
SELECT ARRAY_UNION(ARRAY[1, 1, 2], ARRAY[2, 3, 3]);  -- [1, 2, 3]

-- Merge per-session tag arrays with a canonical tag set
SELECT session_id, ARRAY_UNION(tags, ARRAY['reviewed']) AS tags
FROM analytics.events.user_sessions;

-- NULL on either side returns NULL
SELECT ARRAY_UNION(CAST(NULL AS ARRAY<INT>), ARRAY[1, 2]);  -- NULL

-- NULL elements collapse to a single NULL if present in either side
SELECT ARRAY_UNION(ARRAY[1, CAST(NULL AS INT)], ARRAY[CAST(NULL AS INT), 2]);  -- [1, NULL, 2]

Pitfalls

NULL on either input returns NULL, not an empty or partial result. COALESCE both sides to ARRAY() for NULL-tolerant set arithmetic.
ARRAY_UNION deduplicates. If multiset semantics are required, use ARRAY_CAT to concatenate without removing duplicates.
NULL elements collapse to a single NULL when present in either side. Two-sided NULL presence does not produce two NULL elements.
Element types must be compatible. Mixing STRUCT shapes or differing array nesting levels raises a type error; align schemas before the call.