ARRAY_INTERSECT

All functions > ARRAY > ARRAY_INTERSECT

Returns the intersection of two arrays using set semantics.

Signatures

Returns: An array containing the distinct elements that appear in both input arrays

ARRAY_INTERSECT(array1: ARRAY<T>, array2: ARRAY<T>) → ARRAY<T>

sql

Parameter	Type	Required	Description
`array1`	`ARRAY<T>`	Yes	The first input array
`array2`	`ARRAY<T>`	Yes	The second input array

Notes

Uses set semantics: duplicates in input arrays are removed
Returns only unique elements that exist in both arrays
Result is sorted lexicographically
Returns empty array if no common elements exist
Null arrays are treated as empty arrays

Examples

FeatureQL

SELECT
    f1 := ARRAY_INTERSECT(ARRAY['A', 'B'], ARRAY['B', 'C']),  -- Basic intersection
    f2 := ARRAY_INTERSECT(ARRAY['A', 'B', 'C'], ARRAY['B', 'C']),  -- Multiple matching values
    f3 := ARRAY_INTERSECT(ARRAY['A', 'B', 'B', 'B', 'C'], ARRAY['B', 'B']),  -- Set intersection: returns unique elements only
    f4 := ARRAY_INTERSECT(ARRAY['A', 'A', 'B'], ARRAY['A', 'B', 'B', 'B']),  -- Set semantics: duplicates removed
    f5 := ARRAY_INTERSECT(ARRAY[1, 1, 2, 2, 2], ARRAY[1, 2, 2, 3]),  -- Returns distinct common elements
    f6 := ARRAY_INTERSECT(ARRAY[1, 2, 3], ARRAY[2, 3, 4]),  -- Numeric intersection
    f7 := ARRAY_INTERSECT(ARRAY[1, 2], ARRAY[3, 4]),  -- No intersection
    f8 := ARRAY_INTERSECT(ARRAY['A', 'B'], NULL::ARRAY(VARCHAR)),  -- Null handling
    f9 := ARRAY_INTERSECT(ARRAY[]::VARCHAR[], ARRAY['A', 'B'])  -- Empty array
;

Result

f1 ARRAY	f2 ARRAY	f3 ARRAY	f4 ARRAY	f5 ARRAY	f6 ARRAY	f7 ARRAY	f8 ARRAY	f9 ARRAY
[B]	[B, C]	[B]	[A, B]	[1, 2]	[2, 3]	[]	[]	[]

Suggest changes to this page

Last update at: 2026/03/03 16:47:38

On this page

Signatures Notes Examples