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

EXCEPT ALL

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

EXCEPT ALL is the operation that returns only those values that are returned exclusively in the first subselect without removing duplicates.

SELECT ID FROM BOOK
EXCEPT ALL
SELECT ID FROM AUTHOR

create.select(BOOK.ID).from(BOOK)
      .exceptAll(
create.select(AUTHOR.ID).from(AUTHOR))
      .fetch();

Dialect support

This example using jOOQ:

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

Translates to the following dialect specific expressions:

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

SELECT 
  t.*
FROM (
  SELECT BOOK.ID
  FROM BOOK
  EXCEPT 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!