You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Ognjen Blagojevic <og...@etf.bg.ac.yu> on 2008/03/26 13:14:22 UTC
Is INNER JOIN necessary with ORDER BY?
Hi all,
How can I avoid INNER JOINs to be generated for tables stated in ORDER
BY clause?
For instance, EJB QL
select p
from Person p
order by p.idPlaceOfBirth.name
results in SQL which INNER JOINS Person and Place, and the persons
without known place of birth (id_place_of_birth == null), are not listed.
The only solution I found so far is to explicitly use
select p
from Person LEFT OUTER JOIN p.idPlaceOfBirth b
order by p.idPlaceOfBirth.name
But it tends to unnecessarily add to the EJB QL queries complexity.
Is there any other way to do the same thing?
Regards,
Ognjen
Re: Is INNER JOIN necessary with ORDER BY?
Posted by Ognjen Blagojevic <og...@etf.bg.ac.yu>.
Hi Michael,
I realized that one moment after I sent my first message.
For the archives, the correct query would be:
select p
from Person LEFT OUTER JOIN p.idPlaceOfBirth b
order by b.name
Regards,
Ognjen
Michael Bouschen wrote:
> Hi Ognjen,
>
> I agree with Craig, your solution is correct. I would like to propose a
> small correction: you might want to use the outer join identification
> variable in the order by clause.
> SELECT p FROM Person p LEFT OUTER JOIN p.idPlaceOfBirth b ORDER BY b.name
>
> The order by clause of the outer join example you gave still navigates
> the relationship. An JPA implementation strictly following the spec
> could still generate an inner join for the order by.
>
> Regards Michael
>
>> Hi Craig,
>>
>> Thank you for your answer.
>>
>> We have a Web interface with lots of tables that the user can sort by
>> clicking on the column name. Appropriate action sets the string in the
>> ORDER BY clause of the JPA query. But, from what you wrote, I
>> understand that this is not enough, and that we must also modify the
>> FROM clause.
>>
>> Regards,
>> Ognjen
>>
>>
>> Craig L Russell wrote:
>>> Hi Ognjen,
>>>
>>> As I understand the JPA specification, your analysis and solution are
>>> correct. By the specification, navigating a relationship uses inner
>>> join semantics.
>>>
>>> By the way, JDO uses outer join semantics in query navigation. With
>>> JDO, you would get Persons with null id_place_of_birth unless your
>>> query explicitly had a query clause id_place_of_birth != null. Of
>>> course, the position of the null entries in the result list still
>>> might be before or after non-null entries.
>>>
>>> Regards,
>>>
>>> Craig
>>>
>>> On Mar 26, 2008, at 5:14 AM, Ognjen Blagojevic wrote:
>>>
>>>> Hi all,
>>>>
>>>> How can I avoid INNER JOINs to be generated for tables stated in
>>>> ORDER BY clause?
>>>>
>>>> For instance, EJB QL
>>>>
>>>> select p
>>>> from Person p
>>>> order by p.idPlaceOfBirth.name
>>>>
>>>> results in SQL which INNER JOINS Person and Place, and the persons
>>>> without known place of birth (id_place_of_birth == null), are not
>>>> listed.
>>>>
>>>> The only solution I found so far is to explicitly use
>>>>
>>>> select p
>>>> from Person LEFT OUTER JOIN p.idPlaceOfBirth b
>>>> order by p.idPlaceOfBirth.name
>>>>
>>>> But it tends to unnecessarily add to the EJB QL queries complexity.
>>>>
>>>> Is there any other way to do the same thing?
>>>>
>>>> Regards,
>>>> Ognjen
>>>
>>> Craig Russell
>>> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>>> 408 276-5638 mailto:Craig.Russell@sun.com
>>> P.S. A good JDO? O, Gasp!
>>>
>
>
Re: Is INNER JOIN necessary with ORDER BY?
Posted by Michael Bouschen <mb...@spree.de>.
Hi Ognjen,
I agree with Craig, your solution is correct. I would like to propose a
small correction: you might want to use the outer join identification
variable in the order by clause.
SELECT p FROM Person p LEFT OUTER JOIN p.idPlaceOfBirth b ORDER BY
b.name
The order by clause of the outer join example you gave still navigates
the relationship. An JPA implementation strictly following the spec
could still generate an inner join for the order by.
Regards Michael
> Hi Craig,
>
> Thank you for your answer.
>
> We have a Web interface with lots of tables that the user can sort by
> clicking on the column name. Appropriate action sets the string in the
> ORDER BY clause of the JPA query. But, from what you wrote, I
> understand that this is not enough, and that we must also modify the
> FROM clause.
>
> Regards,
> Ognjen
>
>
> Craig L Russell wrote:
>> Hi Ognjen,
>>
>> As I understand the JPA specification, your analysis and solution are
>> correct. By the specification, navigating a relationship uses inner
>> join semantics.
>>
>> By the way, JDO uses outer join semantics in query navigation. With
>> JDO, you would get Persons with null id_place_of_birth unless your
>> query explicitly had a query clause id_place_of_birth != null. Of
>> course, the position of the null entries in the result list still
>> might be before or after non-null entries.
>>
>> Regards,
>>
>> Craig
>>
>> On Mar 26, 2008, at 5:14 AM, Ognjen Blagojevic wrote:
>>
>>> Hi all,
>>>
>>> How can I avoid INNER JOINs to be generated for tables stated in
>>> ORDER BY clause?
>>>
>>> For instance, EJB QL
>>>
>>> select p
>>> from Person p
>>> order by p.idPlaceOfBirth.name
>>>
>>> results in SQL which INNER JOINS Person and Place, and the persons
>>> without known place of birth (id_place_of_birth == null), are not
>>> listed.
>>>
>>> The only solution I found so far is to explicitly use
>>>
>>> select p
>>> from Person LEFT OUTER JOIN p.idPlaceOfBirth b
>>> order by p.idPlaceOfBirth.name
>>>
>>> But it tends to unnecessarily add to the EJB QL queries complexity.
>>>
>>> Is there any other way to do the same thing?
>>>
>>> Regards,
>>> Ognjen
>>
>> Craig Russell
>> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>> 408 276-5638 mailto:Craig.Russell@sun.com
>> P.S. A good JDO? O, Gasp!
>>
--
Michael Bouschen
akquinet tech@spree GmbH
Tempelhofer Ufer 23-24, 10963 Berlin
Bülowstraße 66, 10783 Berlin
Tel.: +49/(0)30/235 520-33 Fax.: +49/(0)30/217 520-12
Geschäftsführung: Martin Weber, Hendrik Saly, Prof. Dr. Christian Roth
Amtsgericht Berlin-Charlottenburg HRB 86780 B
USt.-Id. Nr.: DE 225 964 680
Re: Is INNER JOIN necessary with ORDER BY?
Posted by Ognjen Blagojevic <og...@etf.bg.ac.yu>.
Hi Craig,
Thank you for your answer.
We have a Web interface with lots of tables that the user can sort by
clicking on the column name. Appropriate action sets the string in the
ORDER BY clause of the JPA query. But, from what you wrote, I understand
that this is not enough, and that we must also modify the FROM clause.
Regards,
Ognjen
Craig L Russell wrote:
> Hi Ognjen,
>
> As I understand the JPA specification, your analysis and solution are
> correct. By the specification, navigating a relationship uses inner join
> semantics.
>
> By the way, JDO uses outer join semantics in query navigation. With JDO,
> you would get Persons with null id_place_of_birth unless your query
> explicitly had a query clause id_place_of_birth != null. Of course, the
> position of the null entries in the result list still might be before or
> after non-null entries.
>
> Regards,
>
> Craig
>
> On Mar 26, 2008, at 5:14 AM, Ognjen Blagojevic wrote:
>
>> Hi all,
>>
>> How can I avoid INNER JOINs to be generated for tables stated in ORDER
>> BY clause?
>>
>> For instance, EJB QL
>>
>> select p
>> from Person p
>> order by p.idPlaceOfBirth.name
>>
>> results in SQL which INNER JOINS Person and Place, and the persons
>> without known place of birth (id_place_of_birth == null), are not listed.
>>
>> The only solution I found so far is to explicitly use
>>
>> select p
>> from Person LEFT OUTER JOIN p.idPlaceOfBirth b
>> order by p.idPlaceOfBirth.name
>>
>> But it tends to unnecessarily add to the EJB QL queries complexity.
>>
>> Is there any other way to do the same thing?
>>
>> Regards,
>> Ognjen
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
Re: Is INNER JOIN necessary with ORDER BY?
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Ognjen,
As I understand the JPA specification, your analysis and solution are
correct. By the specification, navigating a relationship uses inner
join semantics.
By the way, JDO uses outer join semantics in query navigation. With
JDO, you would get Persons with null id_place_of_birth unless your
query explicitly had a query clause id_place_of_birth != null. Of
course, the position of the null entries in the result list still
might be before or after non-null entries.
Regards,
Craig
On Mar 26, 2008, at 5:14 AM, Ognjen Blagojevic wrote:
> Hi all,
>
> How can I avoid INNER JOINs to be generated for tables stated in
> ORDER BY clause?
>
> For instance, EJB QL
>
> select p
> from Person p
> order by p.idPlaceOfBirth.name
>
> results in SQL which INNER JOINS Person and Place, and the persons
> without known place of birth (id_place_of_birth == null), are not
> listed.
>
> The only solution I found so far is to explicitly use
>
> select p
> from Person LEFT OUTER JOIN p.idPlaceOfBirth b
> order by p.idPlaceOfBirth.name
>
> But it tends to unnecessarily add to the EJB QL queries complexity.
>
> Is there any other way to do the same thing?
>
> Regards,
> Ognjen
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!