What joins does SQLite support?

Question

According to the join-op syntax, SQLite has 13 distinct join statements:

,
JOIN
LEFT JOIN
OUTER JOIN
LEFT OUTER JOIN
INNER JOIN
CROSS JOIN
NATURAL JOIN
NATURAL LEFT JOIN
NATURAL OUTER JOIN
NATURAL LEFT OUTER JOIN
NATURAL INNER JOIN
NATURAL CROSS JOIN

Are they all unique? Which are equivalent?

Locomobile

Answer 1

The SQLite grammar is a bit different from the SQL-92 spec's, according to which, the following are illegal:

*OUTER JOIN
*NATURAL OUTER JOIN
*NATURAL CROSS JOIN

The first two, because a <join type>, in order to contain OUTER, must also include an <outer join type> before it. The last, because NATURAL can only occur in <qualified join>'s, not <cross join>'s. These don't appear to behave according to any spec, so it's a good idea to avoid them.

As was answered on the mailing list, SQLite3 only supports three joins: CROSS JOIN, INNER JOIN, and LEFT OUTER JOIN. The following are equivalent:

, == CROSS JOIN
JOIN == INNER JOIN
LEFT JOIN == LEFT OUTER JOIN

As explained in the wikipedia article the NATURAL keyword is shorthand for finding and matching on same-name columns, and doesn't affect the the join type.

According to the SQLite page, 'RIGHT' and 'FULL'  OUTER JOIN's are not supported.

Locomobile

No RIGHT OUTER or FULL OUTER support? – Everetteverette

As of June 26, 2022, SQLite now supports RIGHT and FULL OUTER JOIN in v3.39.0 – Putrescible

Answer 2

An update: In addition to the above JOIN statements, SQLite now supports RIGHT and FULL OUTER JOIN as of 3.39.0 released today (June 26th, 2022): https://sqlite.org/releaselog/3_39_0.html

Metronymic

This is not really an answer to the original question, but would fit as a comment to an answer. – Checkerbloom

I need 50 reputation to comment. – Metronymic