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

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

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])

DuckDB

array_length(array_intersect(
  ARRAY[1, 2],
  ARRAY[2, 3]
)) > 0

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, ClickHouse, DB2, Derby, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Vertica

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website

Feedback

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

The jOOQ Logo