HSQL 2.3.6 outer join syntax

2020-05-27 sql join hsqldb outer-join

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

Answers

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)

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

You are using archaic, bespoke syntax for an outer join. I think you want:

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

You have probably oversimplified your query. As written, there is probably no need for the outer join:

SELECT A.REF
FROM TableA A ;

Related