How to perform outer join in Hsql

Question

I have a query like this

SELECT * from table1 t1,table2 t2, OUTER table3 t3

This works in Informix database but when I use it as Hsql then it is not able to find this "OUTER" keyword only... How to do outer join in Hibernate?

Answer 1

HSQLDB supports Standard SQL syntax, which is different from your Informix syntax query. Some examples of this syntax:

SELECT * from table1 t1 NATURAL JOIN table2 t2 NATURAL LEFT OUTER JOIN table3 t3

SELECT * from table1 t1 JOIN table2 t2 ON (T1.X = T2.Y) LEFT OUTER JOIN table3 t3 ON (T2.Z = T3.X)

You should adapt your query to the Standard syntax.

The query you reported in comments is translated to Standard SQL this way:

SELECT distinct sd.student_id, ss.sports_id, sf.fee_desc FROM STUDENTDETAILS sd
JOIN STUDENTSPORTS ss ON ss.sports_id = sd.sports_id LEFT OUTER JOIN STUDENTFEES sf 
ON ??? 

Note you need a condition for the question marks. For example the id in STUDENTSFEES which needs to be equal to the id in STUDENTSSPORTS. sf.some_id = ss.some_id

Answer 2

assuming you have your entities and mapping files set up correctly it would be a pretty straightforward "left outer join entity.field as alias"

from the HQL website

from Cat as cat
inner join cat.mate as mate
left outer join cat.kittens as kitten

HQL website

In your case taking the tables as entities it would be something like from table1 as tab1 inner join tab.table2column as tab2 left outer join tab.table3 column as tab3