2

In Oracle there is the operator (+), to make an outer join, in a where construct e.g. SELECT * FROM table t, other_table ot WHERE t.id = ot.tale_id(+), is there a similar comfortable way, to do that in a HQL?

SUPPLEMENT: Or is there any other way to make the where condition an outer join?

StuartLC
  • 104,537
  • 17
  • 209
  • 285
Andy_Lima
  • 129
  • 12
  • 6
    I'm sure HQL has some way to specify an outer join (I don't know HQL at all). And you shouldn't be using the `(+)` in Oracle anyway. Even Oracle recommends to use a `left join` in the `from` clause instead. –  Nov 24 '14 at 13:35
  • Thanks for your fast reply but that doesnt answer my question. There are wired "left joins" in hql but i don't want to use them at all ... I only figured out how hql handle implicite joins but I couldnt find a effective way for explicite joins like ... left join – Andy_Lima Nov 24 '14 at 13:37
  • 2
    You: "*How can I use the `(+)` operator in HQL*". Me: "*That's an outer join, I'm sure HQL supports that*", You: "*HQL supports outer joins, but I don't want to use them*", Me: doesn't understand the question any more. –  Nov 24 '14 at 13:40
  • You sayed you have no idea how hql works, I'm really thankful for your help but implicite joins work pretty easy in hql but i found no efficient way for explicite joins. But now we lose the focus complete on my question... – Andy_Lima Nov 24 '14 at 13:45

1 Answers1

5

Is there any way to specify a left outer join in HQL?

Yes, HQL does support left outer joins via explicit join syntax, and the syntax is identical to SQL's (with the implied navigation property as the join key):

from Cat as cat
    left join cat.kittens as kitten

outer is optional, viz left [outer] join

Source: HQL Query Reference

Is there a shorthand abbreviation for left outer join, like Oracle's (+)

No, AFAIK the HQL documentation makes no reference to a shorthand left outer join notation, and if HQL adheres to the trend in RDBMS to remove proprietary join syntax, as per @a_horse_with_no_name's comment, Oracle recommends against using the proprietary (+) syntax, and similarly *= in MSSql Server has been deprecated.

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • Thank you, I already red the Hibernate manual, but that is a implicit join. I want to join explicit. – Andy_Lima Nov 24 '14 at 13:53
  • Use of `left join` syntax is an explicit join. From the docs `implicit join result in inner joins in the resulting SQL statement.`, viz, if you just navigated `cat.kittens` implicitly, it would always be an inner join. – StuartLC Nov 24 '14 at 13:56
  • 2
    @Andy_Lima re-read the document. if you type "left join" in your query, it's obviously an explicit join. An implicit join is something like `where person.address.city = ?`, where you don't explicitely have "join" in the query, but it still makes a join between person and address. – JB Nizet Nov 24 '14 at 13:59
  • I know that i can (explicit) join... But thats out of topic. The question was if there is a similar way like SQL's (+). Not how i can join explicit. – Andy_Lima Nov 24 '14 at 14:03
  • 1
    @Andy_Lima and re-read your previous comment. I quote: *"that is a implicit join."* No, it's not. *"I want to join explicit"* and that's precisely what StuartLC's answer does. How are we supposed to know that when you say "that is an implicit join", you actually mean "that is an explicit join", and that when you say "I want to join explicit", you actually mean "I do not want to use an explicit join"? – JB Nizet Nov 24 '14 at 14:29
  • Sorry, i didn't pay attantion. e.g. 'from Cat as cat join cat.mate as mate <-- implicit left join cat.kittens as kitten <-- explicit' But back to the roots, thats not what i want to know... Anyways mate and kittens are objects in Cat and how sould work that with foreign keys? Maybe I'm just to stupid – Andy_Lima Nov 24 '14 at 14:38
  • Sth. is going wrong here. I ask a question nobody answer it, instead i have to justify why i don't implicit, explicit whatever join. – Andy_Lima Nov 24 '14 at 14:53
  • I guess your question would have been better stated `Is there a shorthand notation for Left Outer Join in HQL, like the (+) syntax in Oracle`. I've updated to reflect both your original question as posted, as well as to address the implied shorthand question from the comments. – StuartLC Nov 24 '14 at 14:56
  • It is pretty easy i want to make my joins in the where condition and i want to tell hibernate to treat the conditions as an outer join. Is that so complicated? – Andy_Lima Nov 24 '14 at 14:56
  • @StuartLC Yes that's my aim. If there are any other solutions than the bad (+), the solution is welcome too. How to do it (except of using joins) I dont mind. I only want to make an outer join out of it. – Andy_Lima Nov 24 '14 at 15:01
  • @Andy_Lima: There is no way in (standard) SQL to specify an "implicit outer join". And that's probably the reason HQL doesn't support it either (there are only two DBMS that have non-standard outer join syntax). I guess the **real** question is: why do you think you need to use the outdated implicit join syntax? –  Nov 24 '14 at 15:05
  • At a wild guess, OP is trying to build up an HQL string as part of a framework and is only making the LOJ decision when parsing the where clause FWR. @Andy_Lima - if applicable, you might look at trying to [Mix Criteria and Hql](http://stackoverflow.com/questions/5135851/mix-hql-and-criteria-api-in-hibernate) and then using the [Criteria LOJ](http://stackoverflow.com/questions/2208201/hibernate-criteria-left-outer-join-with-restrictions-on-both-tables). But that is another question entirely, please. – StuartLC Nov 24 '14 at 15:10
  • I can't use the criteria builder bcz. the query is embeded in a selfmade protocol creator. The database is pretty wild and not compatible with cat.child things... So I only like to know if there is a possibility to make a outer join out of the where condition, not more not less. Maybe that is not abstract enought defined in my question but it is implicit obvisiously. – Andy_Lima Nov 24 '14 at 15:31