Join style and performance

[Philippe Chal�at]

Hello,

Here are two versions of the same query. The first one with theta join 
style, and the second one with ANSI join style. The second one is far 
faster than the first one (these are http://hibernate.org generated 
queries). Is there an explanation for this ?

Thanks

With theta join style

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 ANSI join style :

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