Created
August 28, 2012 10:36
-
-
Save odrotbohm/3497047 to your computer and use it in GitHub Desktop.
JPQL / SQL riddle
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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 formgiven 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 :)