HQL left join of un-related entities

java hibernate hql left-join
ManuPK picture ManuPK · Mar 27, 2012 · Viewed 50.3k times · Source

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

Ken Chan picture Ken Chan · Apr 3, 2012

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.

Related questions

How do you do a limit query in JPQL or HQL?

In Hibernate 3, is there a way to do the equivalent of the following MySQL limit in HQL? select * from a_table order by a_table_column desc limit 0, 20; I don't want to use setMaxResults if possible. This definitely was possible …

Read More
What is the difference between JOIN and JOIN FETCH when using JPA and Hibernate

Please help me understand where to use a regular JOIN and where a JOIN FETCH. For example, if we have these two queries FROM Employee emp JOIN emp.department dep and FROM Employee emp JOIN FETCH emp.department dep Is …

Read More
ORDER BY using Criteria API

When I write a HQL query Query q = session.createQuery("SELECT cat from Cat as cat ORDER BY cat.mother.kind.value"); return q.list(); Everything is fine. However, when I write a Criteria Criteria c = session.createCriteria(Cat.class); …

Read More