CODEWITHSUNDEEP
mysqlsql
DetectiveDrone
DetectiveDrone

Reputation: 41

How does multiple "ON" in a JOIN statement works

I'm currently reading a query that I don't usually see in some other queries when joining tables.

SELECT 
    *
FROM table_1 
    RIGHT OUTER JOIN table_2 
    INNER JOIN table_3 
        ON table_2.some_id = table_3.some_id
    RIGHT OUTER JOIN table_4 
        ON table_3.some_id = table_4.some_id 
    LEFT OUTER JOIN table_5 
        ON table_4.some_id = table_5.some_id 
        ON table_1.some_id = table_4.some_id

Please don't mind how those tables being joined. I just want to know how did that query works? Thank you in advance.

Upvotes: 2

Views: 113

Answers (2)

Laurence
Laurence

Reputation: 10976

I think this is down to the way right outer join is evaluated:

Select
  * 
from 
  a
    right outer join 
  b
    inner join
  c 
    on b.id = c.id 
    on a.id = b.id;

is evaluated as

Select
  * 
from 
  a
    right outer join (
  b
    inner join
  c 
    on b.id = c.id 
  )
    on a.id = b.id;

Mixing left and right outer join is a path to madness.

The OP's query seems to be driven by a desire to exclude parenthesis, or derived tables. It is equivalent to:

Select
    a.id ida,
    b.id idb,
    c.id idc,
    d.id idd,
    e.id ide
From
    D
        left outer join
    A
        on d.id = a.id
        left outer join
    E
        on d.id = e.id
        left outer join (
    B
        inner join
    C
        on b.id = c.id
    ) 
        on d.id = b.id;

Example SQLFiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think the SQL Server documentation captures what is happening better than MySQL. (Here is the documentation.)

This is a parsing issue, as Laurence has observed. You can understand what is by looking at the syntax diagram for the from statement. There is a recursive reference that most people never think about (including me). A joined table has the following syntax:

<joined_table> ::= 
{
    <table_source> <join_type> <table_source> ON <search_condition> 
    | <table_source> CROSS JOIN <table_source> 
    | left_table_source { CROSS | OUTER } APPLY right_table_source 
    | [ ( ] <joined_table> [ ) ] 
}

The key here is the first piece, <table_source> <join_type> <table_source>. Well, guess what, a table_source can be a joined_table (as well as a bunch of other things). This means that the syntax:

A join B join C ON <condition1> ON <condition2>

Fits the grammar above and is interpreted as:

A join (B join C on <condition1>) ON <condition2>

That is, the expression B join C on <condition1> is treated as a "table_source".

My guess is that if both SQL Server and MySQL do it this way, then it is probably part of the standard. However, the standard is a bit harder to understand than SQL Server's syntax diagrams.

Upvotes: 3

Related Questions