HSQL 2.3.6 outer join syntax2020-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
(+) 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
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 ;
- LEFT OUTER JOIN in LINQ
- What is the difference between "INNER JOIN" and "OUTER JOIN"?
- INNER JOIN ON vs WHERE clause
- Difference between JOIN and INNER JOIN
- How to join (merge) data frames (inner, outer, left, right)
- Join vs. sub-query
- LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
- How to do a FULL OUTER JOIN in MySQL?
- Outer JOIN 2 Tables using a third table to filter
- What is the difference between Left, Right, Outer and Inner Joins?