Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

INTERSECT

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

INTERSECT is the operation that produces only those values that are returned by both subselects. By default, this removes duplicate rows. Use INTERSECT ALL in order to retain them, and require duplicates to appear in both subqueries.

SELECT ID FROM BOOK
INTERSECT ALL
SELECT ID FROM AUTHOR
 
create.select(BOOK.ID).from(BOOK)
      .intersectAll(
create.select(AUTHOR.ID).from(AUTHOR))
      .fetch();

Dialect support

This example using jOOQ:

select(BOOK.ID).from(BOOK).intersect(select(AUTHOR.ID).from(AUTHOR)).orderBy(BOOK.ID)

Translates to the following dialect specific expressions:

-- ASE, AURORA_POSTGRES, COCKROACHDB, DB2, DERBY, EXASOL, H2, HANA, HSQLDB, INFORMIX, MARIADB, MYSQL, ORACLE, POSTGRES, 
-- SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA, YUGABYTEDB
SELECT BOOK.ID
FROM BOOK
INTERSECT
SELECT AUTHOR.ID
FROM AUTHOR
ORDER BY ID

-- BIGQUERY
SELECT BOOK.ID
FROM BOOK
INTERSECT DISTINCT
SELECT AUTHOR.ID
FROM AUTHOR
ORDER BY ID

-- MEMSQL
SELECT 
  t.*
FROM (
  SELECT BOOK.ID
  FROM BOOK
  INTERSECT
  SELECT AUTHOR.ID
  FROM AUTHOR
) t
ORDER BY ID

-- ACCESS, AURORA_MYSQL, DUCKDB, FIREBIRD, REDSHIFT
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.19, 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