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

INTERSECT ALL

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

INTERSECT ALL is the operation that produces only those values that are returned by both subselects without removing duplicates.

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).intersectAll(select(AUTHOR.ID).from(AUTHOR)).orderBy(BOOK.ID)

Translates to the following dialect specific expressions:

SELECT BOOK.ID
FROM BOOK
INTERSECT ALL
SELECT AUTHOR.ID
FROM AUTHOR
ORDER BY ID

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

/* UNSUPPORTED */

Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website

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