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
ParameterTypeRequiredDescription
array1ARRAY<T>YesThe first input array
array2ARRAY<T>YesThe 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

See also

Examples

Typical set results

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
;
Result
f1 ARRAYf2 ARRAYf3 ARRAYf4 ARRAYf5 ARRAYf6 ARRAY
[B][B, C][B][A, B][1, 2][2, 3]

Edge cases

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

Suggest changes to this page
Last update at: 2026/05/26 17:22:09