I want to replace the "left outer join" with an alternate basic query, how can I do it?

Question

The query looks like this:

Select t1.*, t2.balance from t1 left outer join t2 on (t1.id1 = t2.id1 and t1.id2 = t2.id2)
where t1.name = 'name';

I was good until I was using native queries but now I need to use Hibernate's JPA implementation for all the queries. The involved table are not associated in any way.

That's why I want to use the alternate fundamental query equivalent to left outer join.

Thanks, Mahesh

Answer 1

My only suggestion would be to UNION the results of two queries, the inner join and then the rows from t1 without a match in t2, something like:

Select t1.*, t2.balance from t1, t2 where t1.name = 'name' and t1.id1 = t2.id1 and t1.id2 = t2.id2
UNION
Select t1.*, null where t1.name = 'name' and (t1.id1,t1.id2) not in (select id1, id2 from t2)
;

I'm not familiar with Hibernate and so don't know if this gives you the same issue. I suppose in the worst case if you really can only execute basic queries then having two independent queries and combining the results in code may have to suffice.