Joining multiple tables with single join clause (sqlite)

Question

So I'm learning SQL (sqlite flavour) and looking through the sqlite JOIN-clause documentation, I figure that these two statements are valid:

SELECT *
FROM table1
JOIN (table2, table3) USING (id);

SELECT *
FROM table1
JOIN table2 USING (id)
JOIN table3 USING (id)

(or even, but that's beside the point:

SELECT *
FROM table1
JOIN (table 2 JOIN table3 USING id) USING id

)

Now I've seen the second one (chained join) a lot in SO questions on JOIN clauses, but rarely the first (grouped table-query). Both querys execute in SQLiteStudio for the non-simplified case.

A minimal example is provided here based on this code

CREATE TABLE table1 (
    id     INTEGER PRIMARY KEY,
    field1 TEXT
)
WITHOUT ROWID;

CREATE TABLE table2 (
    id     INTEGER PRIMARY KEY,
    field2 TEXT
)
WITHOUT ROWID;

CREATE TABLE table3 (
    id     INTEGER PRIMARY KEY,
    field3 TEXT
)
WITHOUT ROWID;

INSERT INTO table1 (field1, id)
VALUES ('FOO0', 0),
       ('FOO1', 1),
       ('FOO2', 2),
       ('FOO3', 3);

INSERT INTO table2 (field2, id)
VALUES ('BAR0', 0),
       ('BAR2', 1),
       ('BAR3', 3);

INSERT INTO table3 (field3, id)
VALUES ('PIP0', 0),
       ('PIP1', 1),
       ('PIP2', 2);

SELECT *
FROM table1
JOIN (table2, table3) USING (id);

SELECT *
FROM table1
JOIN table2 USING (id)
JOIN table3 USING (id);

Could someone explain why one would use one over the other and if they are not equivalent for certain input data, provide an example? The first certainly looks cleaner (at least less redundant) to me.

---

INNER JOIN ON vs WHERE clause has been suggested as a possible duplicate. While it touches on the use of , as a join operator, I feel the questions and especially the answers are more focussed on the readability aspect and use of WHERE vs JOIN. My question is more about the general validity and possible differences in outcome (given the necessary input to induce the difference).

Answer 1

SQLite does not enforce a proper join syntax. It sees the join operator ([INNER] JOIN, LEFT [OUTER] JOIN, etc., even the comma of the outdated 1980s join syntax) separate from the condition (ON, USING). That is not good, because it makes joins more prone to errors. The SQLite docs are hence a very bad reference for learning joins. (And SQLite itself a bad system for learning them, because the DBMS doesn't detect standard SQL join violations.)

Stick to the syntax defined by the SQL standard (and don't ever use comma-separated joins):

FROM table [alias]

((([INNER] | [(LEFT|FULL) [OUTER]]) JOIN table [alias] (ON conditions | USING ( columns ))) | (CROSS JOIN table [alias]))

((([INNER] | [(LEFT|FULL) [OUTER]]) JOIN table [alias] (ON conditions | USING ( columns ))) | (CROSS JOIN table [alias]))

...

(Hope, I've got this right :-) And I also hope this is readable enough :-| I've omitted NATURAL JOIN and RIGHT [OUTER] JOIN here, because I don't recommend using them at all.)

For table you can place some table name or view or a subquery (the latter including parentheses, e.g. (select * from mytable)). Columns in USING have to be surrounded by parentheses (e.g. USING (a, b, c)). (You can of couse use parentheses around ON conditions as well, if you find this more readable.)

In your case, a properly written query would be:

SELECT *
FROM table1
JOIN table2 USING (id)
JOIN table3 USING (id)

or

SELECT *
FROM table1 t1
JOIN table2 t2 ON t2.id = t1.id
JOIN table3 t3 ON t3.id = t1.id

for instance. The example suggests three 1:1 related tables, though. In real life these are extremely rare and a more typical example would be

SELECT *
FROM table1 t1
JOIN table2 t2 ON t2.t1_id = t1.id
JOIN table3 t3 ON t3.t2_id = t2.id

Answer 2

After fixing syntax, these are not the same for all tables, read the syntax & definitions of the join operators in the manual. Comma is cross join with lower precedence than join keyword joins. Different DBMS's SQLs have syntax variations. Read the manual. Some allow naked join for cross join.

using returns only one column for each specified column name & natural is using for all common columns; but other joins are based on cross join & return a column for every input column. So since here tables 2 & 3 have id columns the comma returns a table with 2 id columns. Then using (id) doesn't make sense since one operand has 2 id columns.

If only tables 1 & 3 have an id column, clearly the 2nd query can't join 1 & 2 using id.

There are always many ways to express things. In particular SQL DBMSs execute many different expressions the same way. Research re relational query implementation/optimization in general, in SQL & in your DBMS manual. Generally no simple query variations like these make a difference in execution for the simplest query engine. (We see that in SQLite cross join "is handled differently by the query optimizer".)

First learn to write straightforward queries & learn what the operators do & what their syntax & restrictions are.