ARRAY_INTERSECTION function

Returns a new array containing only the elements that exist in both input arrays. The function is NULL-safe, meaning it treats NULLs are treated as known objects. The order of elements in the result is not guaranteed.

Syntax

ARRAY_INTERSECTION( array1, array2 [, distinct] )

Arguments

array1

A SUPER expression that specifies an array.

array2

A SUPER expression that specifies an array.

distinct

A boolean value that specifies whether to return distinct elements only:

• distinct = FALSE: Multi-set semantics apply. Duplicate elements are preserved, and the frequency of each element in the result equals the minimum of its frequencies in the two input arrays.
• distinct = TRUE: Set semantics apply. Only unique elements common to both arrays are returned, with no duplicates.

The default is FALSE.

Return type

The ARRAY_INTERSECTION function returns a SUPER type.

Example

The following examples show the ARRAY_INTERSECTION function.

SELECT ARRAY_INTERSECTION(ARRAY('a','b','c'), ARRAY('b','c','d'));
 array_intersection 
--------------------
 ["b","c"]
(1 row)

Multi-set semantics:

SELECT ARRAY_INTERSECTION(ARRAY('a','b','b'), ARRAY('b','b','b'));
 array_intersection 
--------------------
 ["b","b"]
(1 row)

Set semantics:

SELECT ARRAY_INTERSECTION(ARRAY('a','b','b'), ARRAY('b','b','b'), TRUE);
 array_intersection 
--------------------
 ["b"]
(1 row)

NULLs are treated as known object.

SELECT ARRAY_INTERSECTION(ARRAY('a',NULL), ARRAY('b',NULL));
 array_intersection 
--------------------
 [null]
(1 row)

See also

• ARRAY_EXCEPT function

• ARRAYS_OVERLAP function

• ARRAY_UNION function

• ARRAY_DISTINCT function