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
Supports ARRAY<ROW> and ARRAY<ARRAY<T>>: both arrays must have the same element type; mismatched schemas raise a user error

Examples

Typical set results

FeatureQL

SELECT
    -- Basic intersection
    f1 := ARRAY_INTERSECT(ARRAY('A', 'B'), ARRAY('B', 'C')),
    -- Multiple matching values
    f2 := ARRAY_INTERSECT(ARRAY('A', 'B', 'C'), ARRAY('B', 'C')),
    -- Set intersection: returns unique elements only
    f3 := ARRAY_INTERSECT(ARRAY('A', 'B', 'B', 'B', 'C'), ARRAY('B', 'B')),
    -- Set semantics: duplicates removed
    f4 := ARRAY_INTERSECT(ARRAY('A', 'A', 'B'), ARRAY('A', 'B', 'B', 'B')),
    -- Returns distinct common elements
    f5 := ARRAY_INTERSECT(ARRAY(1, 1, 2, 2, 2), ARRAY(1, 2, 2, 3)),
    -- Numeric intersection
    f6 := ARRAY_INTERSECT(ARRAY(1, 2, 3), ARRAY(2, 3, 4))
;

Result

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

Edge cases

FeatureQL

SELECT
    -- No intersection
    f1 := ARRAY_INTERSECT(ARRAY(1, 2), ARRAY(3, 4)),
    -- Null handling
    f2 := ARRAY_INTERSECT(ARRAY('A', 'B'), NULL::ARRAY(VARCHAR)),
    -- Empty array
    f3 := ARRAY_INTERSECT(ARRAY()::VARCHAR[], ARRAY('A', 'B'))
;

Result

f1 ARRAY	f2 ARRAY	f3 ARRAY
[]	[]	[]

Suggest changes to this page

Last update at: 2026/06/20 10:08:10

On this page

Signatures Notes See also Examples Typical set results Edge cases