Checks whether Array a and Array b contain the same element.

Syntax

boolean arrays_overlap(array<T> <a>,  array<T> <b>)

Parameters

a and b: required. These parameters specify arrays that T in array<T> specifies the data type of the elements in the arrays. The elements can be of any data type. The elements in Array a and the elements in Array b must be of the same data type.

The following data types are supported:
  • TINYINT, SMALLINT, INT, and BIGINT
  • FLOAT and DOUBLE
  • BOOLEAN
  • DECIMAL and DECIMALVAL
  • DATE, DATETIME, TIMESTAMP, IntervalDayTime, and IntervalYearMonth
  • STRING, BINARY, VARCHAR, and CHAR
  • ARRAY, STRUCT, and MAP

Return value

A value of the BOOLEAN type is returned. The return value varies based on the following rules:
  • If Array a contains at least one element that is in Array b and is not null, true is returned.
  • If Array a and Array b do not contain the same element, both of the arrays are not empty, and one or both of the arrays contain a null element, null is returned.
  • If Array a and Array b do not contain the same element, and both of the arrays are not empty and do not contain a null element, false is returned.

Examples

  • Example 1: Check whether array(1, 2, 3) and array(3, 4, 5) contain the same element. Sample statement:
    -- The return value is true. 
    select arrays_overlap(array(1, 2, 3), array(3, 4, 5));
  • Example 2: Check whether array(1, 2, 3) and array(6, 4, 5) contain the same element. Sample statement:
    -- The return value is false. 
    select arrays_overlap(array(1, 2, 3), array(6, 4, 5));
  • Example 3: One of the arrays contains the null element. Sample statement:
    -- The return value is null. 
    select arrays_overlap(array(1, 2, 3), array(5, 4, null));