Search code examples
sqljoinhsqldbouter-join

HSQL 2.3.6 outer join syntax

I use HSQL 2.3.6 and I want to make a join with the following syntax :

SELECT A.REF FROM TableA A, TableB B
WHERE A.NUM = B.NUM (+)

But in the documentation (most recent of my version of HSQL) http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_joined_table the syntax it's :

<joined table> ::= <cross join> | <qualified join> | <natural join>

<qualified join> ::= <table reference> | [ <join type> ] JOIN <table reference> <join specification>

<join specification> ::= <join condition> | <named columns join>

<join condition> ::= ON <search condition>

<join type> ::= INNER | <outer join type> [ OUTER ]

<outer join type> ::= LEFT | RIGHT | FULL

<join column list> ::= <column name list>

With my syntax I have SQLSyntaxErrorException, and I think it's because my syntax is not compatible.

But not having the documentation for my HSQL's version, I'm not sure. You confirmed ?

Thank you in advance

Solution

  • The (+) is Oracle's proprietary operator for outer joins (which is only supported by Oracle), and even Oracle recommends to stop using it.

    HSQLDB supports the standard LEFT JOIN

    SELECT ...
FROM TableA A
  LEFT JOIN TableB B on A.NUM = B.NUM;

    (not sure I got the direction right, I haven't used Oracle's (+) operator for decades)