Available in versions: Dev (3.20) | Latest (3.19) | 3.18

ARRAY_OVERLAP

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The ARRAY_OVERLAP function allows for checking if two arrays overlap:

SELECT ARRAY[1, 2] && ARRAY[3, 4]
create.select(arrayOverlap(array(1, 2), array(2, 3))).fetch();

The result would look like this:

+---------------+
| array_overlap |
+---------------+
| true          |
+---------------+

Dialect support

This example using jOOQ:

arrayOverlap(array(1, 2), array(2, 3))

Translates to the following dialect specific expressions:

Aurora Postgres, CockroachDB, Postgres, YugabyteDB

(ARRAY[1, 2] && ARRAY[2, 3])

H2

EXISTS (
  SELECT *
  FROM UNNEST(ARRAY[1, 2]) array_table (COLUMN_VALUE)
  INTERSECT
  SELECT *
  FROM UNNEST(ARRAY[2, 3]) array_table (COLUMN_VALUE)
)

HSQLDB

EXISTS (
  SELECT *
  FROM UNNEST(ARRAY[1, 2]) array_table (COLUMN_VALUE)
  INTERSECT ALL
  SELECT *
  FROM UNNEST(ARRAY[2, 3]) array_table (COLUMN_VALUE)
)

Trino

arrays_overlap(
  ARRAY[1, 2],
  ARRAY[2, 3]
)

ASE, Access, Aurora MySQL, BigQuery, DB2, Derby, DuckDB, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Vertica

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo