MySql JOIN syntax with multiple tables

Question

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?

For the performance: I would say that the performance would not differ. The query optimization code will check for the potentially best solution anyway in which order of statements can be changed. However: Run an explain for both solutions and check if the plan is different. — Norbert
– Norbert, Commented Sep 7, 2022 at 20:16
@NorbertvanNobelen thanks for the performance advice, we will definitely check that out. — Josh Drentlaw
– Josh Drentlaw, Commented Sep 7, 2022 at 21:07

Barmar · Accepted Answer · 2022-09-07 20:28:30Z

1

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.

answered Sep 7, 2022 at 20:28

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Josh Drentlaw Over a year ago

thanks for pointing this out. I did come across that page, but sometimes I'm not sure what I'm looking at on there. Your explanation definitely helped.

Barmar Over a year ago

Don't feel bad, it's hard to understand with all the recursive references. It took me several readings before I decided it allowed this.

Rick James · Accepted Answer · 2022-09-09 23:07:48Z

0

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".

answered Sep 9, 2022 at 23:07

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

MySql JOIN syntax with multiple tables

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related