CODEWITHSUNDEEP
hibernatehql
Luixv
Luixv

Reputation: 8710

HQL query equivalence: Why are them different

I have a working HQL query which I want to optimize. It is as follows:

select distinct A.id from Import as A, Place D 
where (A.place=D or A.placeBOK=D) and D.country=? 

I tried to replcae the query from above by the following:

select distinct A.id from Import as A
where A.place.country=? or A.placeBOK.country=?

Besides performance I thought that both queries are equivalent. But they are not. The first is delivering a set of 20 objects whereas the second is delivering only 14 objects.

What am I doing wrong?

Any hint?

Upvotes: 0

Views: 911

Answers (2)

FoxyBOA
FoxyBOA

Reputation: 5846

[UPDATED]

You have to rewrite you query to

select distinct A.id from Import as A LEFT JOIN A.place b LEFT JOIN A.placeBOK c
where b.country=? or c.country=?

Your second query is an equivalent of:

select distinct A.id from Import as A INNER JOIN A.place b INNER JOIN A.placeBOK c
where b.country=? or c.country=?

See also:

14.4. Forms of join syntax

HQL supports two forms of association joining: implicit and explicit.

The queries shown in the previous section all use the explicit form where the join keyword is explicitly used in the from clause. This is the recommended form.

The implicit form does not use the join keyword. Instead, the associations are "dereferenced" using dot-notation. implicit joins can appear in any of the HQL clauses. implicit join result in inner joins in the resulting SQL statement.

from Cat as cat where cat.mate.name like '%s%'

Upvotes: 1

Fortega
Fortega

Reputation: 19702

I don't understand the answer above, but maybe you can try to investigate the 6 results which are resulting from query A but not from query B...

Upvotes: 0

Related Questions