SQL to HQL (Having issue in Left Outer Join)

[Vikas Kapoor]

SQL :

select TA.*,TB.* from tableA TA // Line 1
left outer join (select * from tableB where CB1 = 35236 ) TB //Line 2
on TA.CA=TB.CB // Line 3

Line 1 is easy to convert in HQL.
Line 3 doesn't need to be converted to HQL.
How can I convert Line 2?

Cross Post

[John Bengler]

Hi Vishal,

I'm afraid you have quite a problem here:

Note that HQL subqueries can occur only in the select or where clauses.

Taken from chapter 14.13 of the Hibernate reference:

Hibernate Reference

So you will have to rewrite your query without a subselect - or with the subselect in the select or in the where clause.

If anyone knows a workaround I'm highly interessted, too.


John

[Emanuel Kadziela]

Is there any direct relationship between A and B? Is it expressed in Java, not just in DB? As far as I know, HQL likes property based joins, like Entity A has a field "private B b" and the join is select * from A as a left outer join a.b as B ....

[Rahul Babbar]

Vishal Pandya wrote:SQL :

select TA.*,TB.* from tableA TA // Line 1
left outer join (select * from tableB where CB1 = 35236 ) TB //Line 2
on TA.CA=TB.CB // Line 3

Line 1 is easy to convert in HQL.
Line 3 doesn't need to be converted to HQL.
How can I convert Line 2?

If you want to use Left Outer Join syntax of hibernate, you do not need to use the "On Clause"....hibernate automatically creates this clause based on the relationship between the two mapping files(hbms).

If you do not have a mapping, see whether there should be one..mostly you will find that it should be...

If you decide that there should not be any mapping....and you want to use Left Outer Join in Hibernate, there is one workaround which i know.

select TA.*, (Select TB.* from TableB TB where TB.CB1 = 35236 AND TA.CA = TB.CB) from tableA TA  

Please see here where it was discussed.

However, you should always prefer relationships in mappings unless you have a strong reason to avoid them.

[Vikas Kapoor]

Emanuel Kadziela wrote:Is there any direct relationship between A and B? Is it expressed in Java, not just in DB? As far as I know, HQL likes property based joins, like Entity A has a field "private B b" and the join is select * from A as a left outer join a.b as B ....

Yes there is relationship between two tables in DB and hence it is also reflecting in hbm and pojo.

Here important thing is, I want A X B. There is a direct relationship between A and B as I already mentioned. But I want to apply filter on B table and then I want A X B(filtered).X denotes left outer join.

Now if I do something like,

A a left outer join a.b 

then it would consider all the records of B while applying the join but I want only few records to be considered for join means I have to apply filter before join occurs.

and not mention the difference between,

select TA.*,TB.* from tableA TA // Line 1  
left outer join (select * from tableB where CB1 = 35236 ) TB //Line 2  
on TA.CA=TB.CB // Line 3 

select TA.*,TB.* from tableA TA // Line 1  
left outer join tableB TB //Line 2  
on TA.CA=TB.CB // Line 3 
where TB.CB1 = 35236

[John Bengler]

You're right, as I wrote in my first reply I'm interessted in a real workaround, too, because I encountered the same problems.

And rewriting the query in a way that does exactly the same is something between hard and impossible.

Even this would not return the desired result:

    select TA.*,TB.* from tableA TA // Line 1    
    left outer join tableB TB //Line 2    
    on TA.CA=TB.CB // Line 3   
    where TB.CB1 = 35236
          or TB.CB1 is null

John

[Rahul Babbar]

John Bengler wrote:You're right, as I wrote in my first reply I'm interessted in a real workaround, too, because I encountered the same problems.

And rewriting the query in a way that does exactly the same is something between hard and impossible.

Even this would not return the desired result:

    select TA.*,TB.* from tableA TA // Line 1    
    left outer join tableB TB //Line 2    
    on TA.CA=TB.CB // Line 3   
    where TB.CB1 = 35236
          or TB.CB1 is null

John

Please see here for a solution i mentioned above.
It does exactly the work of a Outer Join in hibernate without using the Join clause.

[Vikas Kapoor]

Rahul, It is not possible to convert your solution into HQL. and if you read my last post carefully I have relationship between two tables.

[Rahul Babbar]

The 'workaround' solution which i mentioned was only when you want to have a Outer Join using hibernate but do not have a relationship between the tables...

[Vikas Kapoor]

Anybody?

[Rahul Babbar]

Vishal Pandya wrote:
Now if I do something like,

A a left outer join a.b 

then it would consider all the records of B while applying the join but I want only few records to be considered for join means I have to apply filter before join occurs.

and not mention the difference between,

select TA.*,TB.* from tableA TA // Line 1  
left outer join (select * from tableB where CB1 = 35236 ) TB //Line 2  
on TA.CA=TB.CB // Line 3 

select TA.*,TB.* from tableA TA // Line 1  
left outer join tableB TB //Line 2  
on TA.CA=TB.CB // Line 3 
where TB.CB1 = 35236

Why would you want to prefer one approach over the other...
As far as i understand, both these queries will get the same result...and will be of the same cost to the DB....!!!
Yes....you can verify it by running Explain Plan on the query.....

Today DBs dont execute the SQL in the same way as your SQL.....DBs can choose to modify the query to appropriate one(which will be better performing and will be effectively be same.)..

Also you would be mistaken if you think "A a left outer join a.b " to "consider all the records of B while applying the join "....

It will not.....The DB does not join the tables initially....but instead filters the row using the where clause, after that i starts to Join the tables.....so that both the above ways are equal performing in terms of cost to the DB...

The bottom line is dont try to second guess as to what exactly the DB will be doing in this case...and both the queries are equally valid.....and have the same cost for the DB.....although given a choice i would prefer to use the second one as it is cleaner and does not include an inner select.....