HQL : Implicit and explicit joins

Philippe Chal · **Joined:** Fri Sep 12, 2003 5:13 am **Posts:** 22

Hello,

Is it possible to have explanation about the following point. I've got a query in which i have an order by clause that makes HIbernate generate an implicit join on tables.

If I change the query with explicit join, the generated SQL is a little bit different : joins become "inner join" with "on" condition, and performances are better (with the DB I'm using).

Hibernate query (implicit joins)

Code:

select 
   m 
from 
   fr.infologic.superviseur.gestionco.modele.Mouvement m 
where 
   m.ik in (:listIk) 
order by  
   m.cde.heurePrep, 
   m.cde.tra.libStd, 
   m.cde.livNom, 
   m.cdeLig.code

SQL query

Code:

select 
   mouvemen0_.ik as ik, 
   mouvemen0_.code as code, 
   [...]
from 
   SV_Mouvement mouvemen0_, 
   SV_Commande commande1_, 
   SV_Transporteur transpor2_, 
   SV_CdeLig cdelig3_
where 
   mouvemen0_.noCde=commande1_.ik and 
   mouvemen0_.noCde=commande1_.ik and 
   commande1_.noTra=transpor2_.ik and 
   mouvemen0_.noCdeLig=cdelig3_.ik and 
   ((mouvemen0_.ik in(? , ? , ?))) 
order by  
   commande1_.heurePrep, 
   transpor2_.libStd,
   commande1_.livNom,
   cdelig3_.code

With explicit JOINs

Hibernate Query (explicit joins)

Code:

select 
   m 
from 
   fr.infologic.superviseur.gestionco.modele.Mouvement m 
join    
   m.cde.tra 
join 
   m.cdeLig 
where 
   m.ik in (:listIk) 
order by  
   m.cde.heurePrep, 
   m.cde.tra.libStd, 
   m.cde.livNom, 
   m.cdeLig.code

SQL Query

Code:

select 
   mouvemen0_.ik as ik,
   mouvemen0_.code as code, 
from 
   SV_Mouvement mouvemen0_ 
inner join 
   SV_Commande commande1_ 
on 
   mouvemen0_.noCde=commande1_.ik 
inner join 
   SV_Transporteur transpor2_ 
on 
   commande1_.noTra=transpor2_.ik 
inner join 
   SV_CdeLig cdelig3_ 
on 
   mouvemen0_.noCdeLig=cdelig3_.ik 
where 
   (mouvemen0_.ik in(? , ? , ?)) 
order by  
   commande1_.heurePrep,
   transpor2_.libStd,
   commande1_.livNom,
   cdelig3_.code

A general way, I'd like to know when Hibernate generates "inner join" with "on" condition and when it uses the syntax with "," and "where" condition.

Thanks

Philippe

emmanuel · **Posted:** Thu Jan 29, 2004 6:41 pm

is hibernate.use_outer_join=true ?

Philippe Chal · **Joined:** Fri Sep 12, 2003 5:13 am **Posts:** 22

emmanuel wrote:

is hibernate.use_outer_join=true ?

The line is in comment in hibernate.properties. As we are using SAPDB Dialect, I think it is set to TRUE.

From SAPDB dialect :
getDefaultProperties().setProperty(Environment.USE_OUTER_JOIN, "true");

But isn't the problem is more about the syntax of inner join ?

Thanks

emmanuel · **Posted:** Fri Jan 30, 2004 1:29 pm

Actually I'm not very familiar with this part of Hibernate

gavin · **Posted:** Mon Feb 02, 2004 2:01 pm

* HQL implicit where-clause joins result in SQL theta-style inner joins
* HQL explicit from-clause joins result in SQL ANSI-style joins

Philippe Chal · **Joined:** Fri Sep 12, 2003 5:13 am **Posts:** 22

Thanks for the answer.

Is the "cohabitation" historical, or does it have a special meaning ?

Thanks again

Philippe