Using a full outer join in HSQLDB

Question

Hi,
Is a full outer join supported in HSQLDB. I have been trying to use it but it doesnot work. I am trying to do something like this.

1. Query 1 pulls data from table A as key,value
2. Query 2 pulls data from table B as Key,value Now i needed an outer join where by i will club the data between the two where the keys matched and also get data where the keys didnot match. In case outer join is not supported in HSQLDB (we are using hsqldb and cannot change the database), what is the best approach should i use to implement this efficiently.

Thanks.

Answer 1

FULL OUTER JOIN is supported in HSQLDB 2.0. For older versions use this

SELECT * FROM (
SELECT table1.key, table1.value, table2.value FROM table1 LEFT OUT JOIN table2 ON table1.key = table2.key
UNION 
SELECT table1.key, table1.value, table2.value FROM table2 LEFT OUT JOIN table1 ON table1.key = table2.key
)