Basic examples of legal joins, with optional table aliases included:
select * from foo as f JOIN bar as b
select * from foo as f INNER JOIN bar as b
select * from foo as f LEFT JOIN bar as b
select * from foo as f RIGHT JOIN bar as b
select * from foo as f FULL JOIN bar as b
select * from foo as f LEFT OUTER JOIN bar as b
select * from foo as f RIGHT OUTER JOIN bar as b
select * from foo as f FULL OUTER JOIN bar as b
For all of the above, one could append
ON f.id = b.id
or
USING (id)
to specify join condition or columns. For a natural join, you cannot specify columns (eg no ON or USING):
select * from foo NATURAL JOIN bar
Hope that helps.
PS-it looks like I do need to support USING.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Note about above: you do need one of the ON or USING appended to all the joins (except natural) examples above. Not sure if that was clear... so "one must append" :)
A more legal example with 3 tables:
select * from foo as f JOIN bar as b ON f.id=b.id
JOIN baz as z ON f.id=z.id
In such cases, one can use parentheses around a joined table, eg:
select * from foo as f JOIN
(bar as b JOIN baz as z ON f.id=z.id)
ON f.id=b.id
which can be important if the types of joins differ, eg:
select * from foo as f LEFT JOIN
(bar as b RIGHT JOIN baz as z ON f.id=z.id)
ON f.id=b.id
In all of these, the various join qualifiers could be used, but this is just to illustrate how ( ) can also fit in (0.4.5 doesn't currently like them either).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Using 0.4.5, the parser does not allow all of the join qualifiers I expect. For the few I want to allow/use, my book on SQL92 says:
natural join ::= table1 [NATURAL] [join type] JOIN table2
predicate join ::= table1 [join type] JOIN table2
join type::= INNER | { { LEFT| RIGHT | FULL} [OUTER] }
but JSqlParser 0.4.5 only seems to allow INNER and LEFT.
Patrick
PS-I don't really need the other types: CROSS JOIN, UNION JOIN, or JOIN ... USING.
Please post at least 2 examples for each join type (as complex as you want) so that I can put them in the test suite.
Basic examples of legal joins, with optional table aliases included:
select * from foo as f JOIN bar as b
select * from foo as f INNER JOIN bar as b
select * from foo as f LEFT JOIN bar as b
select * from foo as f RIGHT JOIN bar as b
select * from foo as f FULL JOIN bar as b
select * from foo as f LEFT OUTER JOIN bar as b
select * from foo as f RIGHT OUTER JOIN bar as b
select * from foo as f FULL OUTER JOIN bar as b
For all of the above, one could append
ON f.id = b.id
or
USING (id)
to specify join condition or columns. For a natural join, you cannot specify columns (eg no ON or USING):
select * from foo NATURAL JOIN bar
Hope that helps.
PS-it looks like I do need to support USING.
Note about above: you do need one of the ON or USING appended to all the joins (except natural) examples above. Not sure if that was clear... so "one must append" :)
A more legal example with 3 tables:
select * from foo as f JOIN bar as b ON f.id=b.id
JOIN baz as z ON f.id=z.id
In such cases, one can use parentheses around a joined table, eg:
select * from foo as f JOIN
(bar as b JOIN baz as z ON f.id=z.id)
ON f.id=b.id
which can be important if the types of joins differ, eg:
select * from foo as f LEFT JOIN
(bar as b RIGHT JOIN baz as z ON f.id=z.id)
ON f.id=b.id
In all of these, the various join qualifiers could be used, but this is just to illustrate how ( ) can also fit in (0.4.5 doesn't currently like them either).
I think the other types of join now should work (0.5.1) but I'm still working on the "( )" issue.
I added a new class, SubJoin, to handle the case:
select * from foo as f LEFT JOIN (bar as b RIGHT JOIN baz as z ON f.id=z.id) ON f.id=b.id
It's in 0.5.2
Please let me know if it works.