CODEWITHSUNDEEP
mysqljoinsyntax
Josh Drentlaw
Josh Drentlaw

Reputation: 11

MySql JOIN syntax with multiple tables

Can someone explain why in the first solution for this SO answer the LEFT JOIN can be nested between the INNER JOIN table name and ON clause?

SELECT Persons.Name, Persons.SS, Fears.Fear FROM Persons
LEFT JOIN Person_Fear
    INNER JOIN Fears
    ON Person_Fear.FearID = Fears.FearID
ON Person_Fear.PersonID = Persons.PersonID

I can't find anything describing this syntax and I'm not sure what the point is. The answer has 2 solutions, and I would've naturally done the second solution having never seen the first solution's syntax before.

Is there performance benefits for either?

Upvotes: 0

Views: 54

Answers (2)

Rick James
Rick James

Reputation: 142296

I would add parens to make it clear:

SELECT  Persons.Name, Persons.SS, Fears.Fear
    FROM  Persons
    LEFT JOIN  ( Person_Fear
                  INNER JOIN  Fears
                        ON Person_Fear.FearID = Fears.FearID
               )
        ON Person_Fear.PersonID = Persons.PersonID

or

SELECT  Persons.Name, Persons.SS, Fears.Fear
    FROM  ( Persons
               LEFT JOIN  Person_Fear
                  ON Person_Fear.PersonID = Persons.PersonID
          )
    INNER JOIN  Fears  ON Person_Fear.FearID = Fears.FearID

depending which you wanted.

Then I would get EXPLAIN SELECT ... to see if it parsed it "correctly".

Upvotes: 0

Barmar
Barmar

Reputation: 781096

The parameters to the FROM keyword are what the MySQL documentation calls table_references, and its syntax is described here. There's lots of recursive references in the syntax, and I think this is what allows that syntax. I've copied what I think are relevant excerpts from the BNF.

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}

The nested

    Person_Fear
    INNER JOIN Fears
    ON Person_Fear.FearID = Fears.FearID

is a joined_table, which can be used as the table_reference in the first LEFT JOIN.

Upvotes: 1

Related Questions