JPA criteria query: subquery in where clause with predicate "in"

Question

I have the following design (pseudo code):

table A (id int, cond int)
table B (id int, a_id int)

That is, B.a_id is a foreign key to A.id. Not all entries in A are referenced by B. B contains many more entries than A.

The relation between A and B is only expressed in B, not in A:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "a_id")
private A a;

I want to build a JPA criteria query for this SQL (query 1)

select A.* from A where A.id in (select distinct B.a_id from B) or A.cond = 1

At the moment I found this solution:

EntityManager em = ...
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<A> qa = cb.createQuery(A.class);
Root<A> ra = qa.from(A.class);

Subquery<B> sqb = qa.subquery(B.class);
Root<B> srb = sqb.from(B.class);

qa = qa.select(ra).distinct(true).where(cb.or
    ( 
      cb.equal(ra, srb.get(B_.a))
    , cb.equal(ra.get(A_.cond), 1)
    ));

em.createQuery(qa).getResultStream().forEach(System.out::println);

EclipseLink translates this into a join between A and B and a final distinct selection (query 2)

SELECT DISTINCT t0.ID, t0.COND FROM A t0, B t1 WHERE ((A.ID = B.A_ID) OR (A.COND = ?))
  bind => [1]

However, because B has so many entries I wonder if a first select distinct B.a_id from B would be better.

What is the corresponding JPA criteria query expression for query 1 with a subquery in the where clause?

(I would like to know the solution although the database have a query optimizer and may select the estimated best execution path)

EDIT

In the case of my data and indexes the query optimizer chooses an execution plan for query 1 with estimated costs 3 while it estimates costs of 7 for query 2.

There is another form (query 3)

select A.* from A where exists (select 1 from B where B.a_id = A.id) or A.cond = 1

for which the query optimizer chooses the same execution plan as for query 1. With criteria builder it is

qa = qa.select(ra).distinct(true).where(cb.or
    ( 
      cb.exists(qb.select(rb).where(cb.equal(ra, rb.get(B_.a))))
    , cb.equal(ra.get(A_.cond), 1)
    ));

JPA criteria query: subquery in where clause with predicate "in"

0 Answers0