phpmyadmin Question posted in
The official documentation can be found here.

Why does this "nested join" works with PDO but not with MySql cli? – Phpmyadmin

I don’t understand why this query works and what it actually means. It should be noted that this query works when using PDO but doesn’t through Mysql cli or even phpmyadmin. 

   SELECT table1.something, table2.something 
   FROM someTable
   LEFT JOIN table 1
     INNER JOIN table2
       ON table2.table1_id = table1.id
     ON table1.account_id = someTable.account_id

No errors are thrown and, even weirder, I actually have results that are coherent with what the query is supposed to do.

Tags:

2

Answers


  1. 0

    This is valid SQL syntax, assuming you have just taken a snippet from the FROM clause. It is interpreted as:

    SELECT table1.something, table2.something
FROM someTable
LEFT JOIN
(table1 INNER JOIN
 table2 bbm
 ON table2.table1_id = table1.id
)
ON table1.account_id = table2.account_id

    That said, this is really arcane, because the alias renames table2 to bbm, which is not used. The ON conditions are only referring to earlier tables. The result is some strange form of CROSS JOIN.

    That you can nest JOINs this way should — in my opinion — be merely viewed as an amusement. Don’t nest JOINs. Each JOIN should be followed by its very own ON clause, before the next table/subquery reference. Nesting joins makes the code is harder to understand. It can introduce errors (which I think happened in this case). There can also be subtle edge cases where it is a little challenging to figure out what is happening.

    Login or Signup to reply.
  2. 0

    There are two syntax variations in Standard SQL for writing multiple joins, the common one:

    a join b on ... join c on ... join d on ...

    and the strange one:

    a join b join c join d on ... on ... on ...

    In both cases the first ON is processed first, which means the strange syntax joins the first table in the last ON and the last table in the first ON, which is really hard to follow. That’s why almost nobody is using it (but some tools might create it)

    To change your strange

    SELECT table1.something, table2.something 
FROM someTable
LEFT JOIN table1
  INNER JOIN table2 bbm
  ON table2.table1_id = table1.id
ON table1.account_id = someTable.account_id

    to the common one move the last ON after the first join:

    SELECT table1.something, table2.something 
FROM someTable
LEFT JOIN table1
  ON table1.account_id = someTable.account_id
INNER JOIN table2 bbm
  ON table2.table1_id = table1.id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search