CODEWITHSUNDEEP
javasqljooq
KarateKid
KarateKid

Reputation: 3436

JOOQ: Select keys from a table not present in another table

How to get all the keys in one table that are not present in another table in jooq? Or what is will be equivalent of the following SQL command:

SELECT ID FROM A WHERE ID NOT IN (SELECT ID FROM B)

Upvotes: 2

Views: 1443

Answers (4)

Liudmila Udot
Liudmila Udot

Reputation: 1

Better use left join in that case, it is just one query 

SELECT A.ID FROM A LEFT JOIN B ON A.ID=B.ID WHERE B.ID IS NULL;

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220952

This would be a 1:1 translation to jOOQ:

DSL.using(configuration)
   .select(A.ID)
   .from(A)
   .where(A.ID.notIn(select(B.ID).from(B)))
   .fetch();

The above is assuming a static import as such:

import static org.jooq.impl.DSL.*;

A note on using NOT IN

The SQL NOT IN predicate only works correctly when the subquery does not yield any NULL values. In the presence of a single NULL value, the entire predicate will not return any rows.

It is often better to use NOT EXISTS as shown in this answer. Or in jOOQ:

DSL.using(configuration)
   .select(A.ID)
   .from(A)
   .where(notExists(selectOne().from(B).where(B.ID.eq(A.ID))))
   .fetch();

Upvotes: 4

Alexandru
Alexandru

Reputation: 14

Select id from A
   Minus
Select id from B; -- all from A where not in B

Select id from B
  Minus
Select id from A; -- all from B where not in A

Upvotes: 0

xycf7
xycf7

Reputation: 913

Try this:

SELECT ID FROM A WHERE not exists (SELECT ID FROM B where B.ID = A.ID )

Upvotes: 0

Related Questions