Skip to content

Instantly share code, notes, and snippets.

@odrotbohm
Created August 28, 2012 10:36
Show Gist options
  • Save odrotbohm/3497047 to your computer and use it in GitHub Desktop.
Save odrotbohm/3497047 to your computer and use it in GitHub Desktop.
JPQL / SQL riddle
JPQL: select p from Person p
left outer join p.address address
order by address.city
SQL: select person0_ from Person person0_
left outer join Address address1_ on person0_.id=address1_.person_id
order by address1_.city
JPQL: select p from Person p
left outer join p.address address
order by p.address.city
SQL: select person0_ from Person person0_
left outer join Address address1_ on person0_.id=address1_.person_id
cross join Address address2_ where person0_.id=address2_.person_id
order by address2_.city
The core difference here is that qualifying the sorting criteria to p.address.city
instead of address.city results in an additional JOIN clause added which results in
persons not having an address being ruled out from the result set. Environment is
Hibernate 4.1.5.SP1, H2 1.3.168.
1. Should the two JPQL queries result in the SQL queries shown? In particular
shouldn't they actually generate the very same SQL?
2. Why does the second JPQL query add the additional JOIN which effectively rules
out persons without an address?
3. Is it a good idea that adding a sort criterion potentially adds an additional
join which influences the actual result set return (i.e. "the number of results
changes just because I added a sort criteria")
@rgielen
Copy link

rgielen commented Aug 29, 2012

Regarding your comment to the addresses-example:

Of course it should have read ORDER BY p.addresses. The example does make sense as a classic "map rows to columns" example, relying on an effective query planner and executer in the DBMS, causing least possible communication overhead between DB and JVM. More realistic would be: Generate result rows of form

firstname, lastname, home phone, business phone

given that phone numbers are attributes of an entity with 1:n relation to Persons and a discriminator column type

But again, the example not really good to make any case for my point in opposite to yours, as mentioned in my last comment - so forget about it :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment