SQLite natural join broken?

I am just getting to know NATURAL JOIN and SQLite is not behaning as I expect.

SELECT * FROM r1 NATURAL JOIN (r2 NATURAL JOIN r3);

and

SELECT * FROM (r1 NATURAL JOIN r2) NATURAL JOIN r3;

produce the same (correct) results.

However if I omit the parentheses as in:

SELECT * FROM r1 NATURAL JOIN r2 NATURAL JOIN r3;

I see that r1 and r2 are joined correctly however r3 is not joined to the result at all, instead the cartesian product of r1 NATURAL JOIN r2, r3 is formed.

Is there an issue with the attribute names of the first join result, or am I misinterpreting SQL?

Answer

I don’t use that database myself, but according to this documentation, all joins in SQLite are based on the cartesian product of the left and right tables.

NATURAL joins use common column names as “keys” to combined two tables. Using parentheses forces a “derived table” to be built before the outer join is processed. Without parentheses, it does not “see” the common column names, so the second NATURAL keyword is ignored.

A little advice: it’s good to understand the mechanics of how a NATURAL JOIN works in case you ever see it in code, but do not use it yourself. It is a “dangerous” construct with little value. Just my opinion.