How to translate on HQL MSSQL MINUS: LEFT JOIN

volod · **Joined:** Wed Nov 10, 2004 11:25 am **Posts:** 11

Hello.

I have an SQL query that implements MSSQL's MINUS operation:

Code:

SELECT tbl1.*
FROM table tbl1 
LEFT JOIN tbl2 ON tbl1.key=tbl2.key 
WHERE tbl2.key IS NULL

Hibernate version:2.1.8

How to translate it on HQL?
The problem is: tbl2 should be mapped to a collection. But I don't have it and don't need it.
Thanks a lot.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Well, the literal equivalent would be:

Code:

select tbl1
from Table1 tbl1, Table2 tbl2
where tbl1.key = tbl2.key
  and tbl2.key is null

But let's face it, that query doesn't need to be that complicated. Omit Table2 entirely:

Code:

from Table1 where key is null

volod · **Joined:** Wed Nov 10, 2004 11:25 am **Posts:** 11

Just noticed that this query is not what I need.

Code:

select tbl1
from Table1 tbl1, Table2 tbl2
where tbl1.key = tbl2.key
  and tbl2.key is null

Is a JOIN, but not LEFT JOIN
LEFT JOIN and JOIN differes a lot

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I think you'd need a subquery for that:

Code:

from Table1 tbl1
where tbl1.key is null
  or tbl1.key not in (select tbl2.key from Table2 tbl2)

volod · **Joined:** Wed Nov 10, 2004 11:25 am **Posts:** 11

:-))
That's what I have now. But I've found left join to work faster than IN and this query is very critical for speed. There is a way to do it with SQL (not HQL), but I hope to do it with HQL.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

There is no way to perform any theta non-inner join in HQL (see HIA, section 7.3.5: last line). If you want to use a left join, you'll have to map the relationship between the two tables. If you can't do that, you'll have to use an SQL query instead of an HQL query.