HQL left join of un-related entities

Question

I have 2 entities, A and B. They are related but I do not want to add the relationship mapping to the beans.

How can we use left outer join between A and B using HQL or criteria?

There are some workarounds available for this,

1. Use Native SQL as told here.
2. Add a relationship and use select a from A a left join a.b.
3. We can do a inner join in the HQL as select * from A a, B b where a.some=b.some

I was always going back these 2 options, is there any alternative for this? Or this in not possible?

Answer 1

Currently, the theta-style on joining the unrelated classes in the where clause using HQL only supports inner join.

The request for supporting the outer join for such situation is currently the 3-rd most voted enhancement but I don't think this feature will be implemented in the near feature as it requires the re-implementation of the current ANTLER-based query parser first which seems to be a gigantic task IMO.

If you insist to use the HQL to perform left join without adding the relationship between A and B , you can use option 3 to do the inner join first, then use the following HQL

from A a where a.some not in ( select b.some from B)

to find out all the A that cannot join B and combine the results programmatically .

Update

As of release 5.1.0 HHH-16 (Explicit joins on unrelated classes) is fixed and we should be able to join the unrelated entities.

Answer 2

As Ken Chan said, you can't do it directly in a single HQL query.

Concerning your three possibilities:

1. Native SQL: Not recommendable. The syntax for outer joins is quite different between different databases.
2. Add a relationship: That's what I would do. It does not cost much code or memory and it is quickly programmed.
3. Inner join: That does not work (missing rows) if the relation really is an outer join in the database.

If by any special reasons you really don't want to add the relationship, you can split the query into two individual queries and join the result manually in java, for example like this:

Query qa = session.createQuery("from A a");
List la = qa.list();

Query qb = session.createQuery("select distinct b.* from B b, A a where a.some=b.some");
List lb = qb.list();

Map bMap = new HashMap();
for (B b : lb) {
  bMap.put(b.getId(), b);
}

/* example with for loop */
for (A a : la) {
  B b = bMap.get(a.getForeignKeyForB());
  /* now you have A a and the outer joined B b and you can do with them what you want */
  ...
}

This solution has (nearly) the same cost in execution time and memory as the outer join in the database (solution 2.). It is just a little bit more java code.

(The solution is similar to that one proposed by Ken Chan, but it avoids the "not in" and the inner select, which both can be inefficient in the database.)

Answer 3

If you know that for every A there is maximum 1 B, you can also use a subquery.

For example:

select a, (select b from B b where b.some = a.some)
from A a

If you know that there exists at least 1 B, you can also use the following query, but it is not recommended as it is a hack:

select a, (select b4 from B b4 where b4=b and b4.some=a.some) from A a, B b 
where a.some=b.some or (a.some not in (select b2.some from B b2) 
and b.id = (select min(b3.id) from B b3))

Answer 4

Bold statement: You can't.

Why? JPQL (and HQL) expects a path between the entities in order to join them.

What I usually do in a situation like this one of (preferred order):

1. Associate the the entities.
2. Fetch the data you need to assemble the results programmatically.
3. Either 1 or 3 of the three options you already mentioned.