4

I'm wondering if a 'normal' inner join leads to higher execution performance in MySQL queries than a simplistic query where you list all tables and then join them with 'and t1.t2id = t2.id' and so on ..

Jörg Haubrichs
  • 2,215
  • 3
  • 24
  • 26
  • For reference to anyone else coming across this down the road like I did... http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause provides a great deal of detail on the differences and benefits. – JeopardyTempest Jul 28 '16 at 10:10

1 Answers1

6

The execution plan and runtime is the same. One is called ANSI style (INNER JOIN, LEFT, RIGHT) the other is called Theta style.

These two queries are equivalent in every way to mysql server

SELECT * FROM A INNER JOIN B ON A.ID = B.ID;

SELECT * FROM A, B WHERE A.ID = B.ID;

You can test this by typing EXPLAIN in front of both queries and the result returned should be the same.

Yada
  • 30,349
  • 24
  • 103
  • 144
  • @yada I would be interested in more info. I'm guessing they are the same because of mysql's optimization? – corymathews Jan 20 '10 at 15:02
  • 1
    It just a difference in style. Just like how there are two ways to make comments in SQL -- or /* */. Search google for Theta vs ANSI SQL Style. http://www.86db1.com/sql-join-syntax-theta-vs-ansi – Yada Jan 20 '10 at 15:39
  • 1
    FWIW, both syntax forms are supported in the ANSI SQL standard. – Bill Karwin Jan 20 '10 at 16:41
  • 1
    Thank you, that was even more than I asked for.I didnt know that it is even specified as the same. – Jörg Haubrichs Jan 20 '10 at 16:52