Difference between JOIN...ON...JOIN...ON vs JOIN...JOIN...ON...ON

Question

What, if any, is the difference between the following?

FROM table1
LEFT JOIN table2 ON table1.key = table2.key
LEFT JOIN table3 ON table2.key = table3.key

and:

FROM table1
LEFT JOIN table2
    LEFT JOIN table3
    ON table2.key = table3.key
ON table1.key = table2.key

Results seem to be the same.

As there are comments and answers that say second syntax is invalid I'll clarify - SQL Server, PostgreSQL and MySQL accept both variants just fine. I also work with a platform that has it's own implementation of TSQL that translates it's own implementation into the one of the provider it's actually connected to, and the second variant is how it writes the code from visual editor by default.

After the first answer i decided to add a more complex example of the second type:

SELECT table1.Name AS MainData, table3ParentParent.Name AS AdditionalData
FROM table1
LEFT JOIN table2
    JOIN table3
        JOIN table3 AS table3Parent
            JOIN table3 AS table3ParentParent
            ON table3Parent.Parent = table3ParentParent.key
        ON table3.Parent = table3Parent.key
    ON table2.table3_id = table3.key
ON table1.table2_id = table2.key

I've changed the names of tables to meaningless since real ones are in Russian and wouldn't tell you anything anyway, but that's part of a query I work on right now. Also, I'd like to point out that I'm not asking advice on optimizing the query, if someone finds that it's suboptimal, I'm looking to understand if there's a practical difference to syntax, or only a cosmetical one.

Isogamete

Coding syntax is different I think @misamoto – Bunce

Coding syntax only and harder in second case. Almost everyone prefers first one. – Heerlen

Q1 is valid, Q2 is invalid (syntax error.) – Dissimilitude

The second query is invalid and should be rejected by the DBMS. If you are not getting an error, your DBMS silently converts this somehow, which it shouldn't. – Bascom

MSSQL and PostgreSQL both accept both syntaxes just fine. Added the clarification to the question. – Isogamete

Also works in MySQL. Both queries are identical. – Kennie

Well, there might be some subtler inner-workings difference, hence, the question – Isogamete

@Isogamete i don't think so, but I have no proof. I believe it is syntactically equal to FROM table1 LEFT JOIN (table2 LEFT JOIN table3 ON table2.key = table3.key) ON table1.key = table2.key, the optimizer has final say on which order the rows are joined. – Kennie

@Isogamete tested on SQL Server, both queries produce same results and plan. – Kennie

Answer 1

In some databases, the "grammar" for a join expression is defined recursively. Simplified examples below:

MySQL

join_table:
    table_reference [INNER|CROSS] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition


table_reference:
    table_factor
  | join_table


table_factor:
    tbl_name

SQL Server

<joined_table> ::=
{
    <table_source> <join_type> <table_source> ON <search_condition>
    | <table_source> CROSS JOIN <table_source>
}


<table_source> ::=
{
    table_or_view_name
    | <joined_table>
}


<join_type> ::=
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } ] JOIN

This means that the following is perfectly valid according to the grammar:

FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.key = t3.key ON t1.key = t2.key

The grammar also explains that the above query is identical to:

FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.key = t3.key) ON t1.key = t2.key

You can convert the complex queries to simpler ones based on the above information. The following query:

SELECT *
FROM table1
LEFT JOIN table2
    JOIN table3
        JOIN table3 AS table3Parent
            JOIN table3 AS table3ParentParent
            ON table3Parent.Parent = table3ParentParent.key
        ON table3.Parent = table3Parent.key
    ON table2.table3_id = table3.key
ON table1.table2_id = table2.key

Could be written as:

SELECT *
FROM table1
LEFT JOIN (table2
    JOIN (table3
        JOIN (table3 AS table3Parent
            JOIN table3 AS table3ParentParent
            ON table3Parent.Parent = table3ParentParent.key)
        ON table3.Parent = table3Parent.key)
    ON table2.table3_id = table3.key)
ON table1.table2_id = table2.key

After expanding brackets, starting from the inside, you get the following (notice that LEFT becomes RIGHT):

SELECT *
FROM table3 AS table3Parent
JOIN table3 AS table3ParentParent ON table3Parent.Parent = table3ParentParent.key
JOIN table3 ON table3.Parent = table3Parent.key
JOIN table2 ON table2.table3_id = table3.key
RIGHT JOIN table1 ON table1.table2_id = table2.key

Kennie

Answer 2

The first is evaluated as:

FROM (table1 LEFT JOIN
      table2
      ON table1.key = table2.key
     ) LEFT JOIN
     table3
     ON table2.key = table3.key

The second is evaluated as:

FROM table1 LEFT JOIN
     (table2 LEFT JOIN
      table3
      ON table2.key = table3.key
     )
     ON table1.key = table2.key

So, the LEFT JOIN between table2 and table3 is evaluated first. In the first query, it is evaluated second.

Does this make a difference? In this case, I don't think there is a difference. There are some other combinations of JOINs where the ordering can make a difference, in subtle edge conditions.

Lap

I would like someone to explain a more in-depth usage. The question is theoretical, since I don't have a concrete example, but in practice I of cource do encounter more complex nesting. I'll add something to the question – Isogamete