MySQL : which one is faster and why?

Question

Which one of the following queries is prefered? And why? They accomplished exactly the same thing.

select e1.Name from Employee e1, Employee e2
    where e1.ManagerId = e2.Id and e1.Salary > e2.Salary

or

select e1.Name from Employee e1 join Employee e2
       on e1.ManagerId = e2.Id and e1.Salary > e2.Salary

I have heard using JOIN is better but it seems to be slightly slower.

Those two queries should execute identically; the issue is the the "comma join" notation is generally harder to read (and maintain), and has been out of favor for decades. Simplest example. Imagine a 3+ table "comma" join with all join conditions in a single WHERE mixed in with a few non-join conditions, in no particular order. Now image the same but with explicit joins, with join conditions in corresponding ON clauses and general conditions in the WHERE. ...Now imagine you need to modify those queries to have one of the joins be a "left" join. — Uueerdo, Jun 14 '17 at 20:35
What results do you get when you measure them? Speculating about performance by looking at source (particularly source code that undergoes as much processing as SQL) is rarely worthwhile. — Dale Wilson, Jun 14 '17 at 20:36
check out: https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause — Samuel Cook, Jun 14 '17 at 20:36
I would chalk up any performance difference between those two queries to RDBMS and file-system caching. — Uueerdo, Jun 14 '17 at 20:40

score 0 · Answer 1 · answered Jun 29 '17 at 04:03

They are identical in this case. Add LEFT and all bets are off.

You can discover that they are identical by doing this to each:

EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;

You will see that one variant is transformed to look identical to the other.

Since the semantics of the two formulations are identical (without out LEFT) the Optimizer treats them identically.

But please use JOIN ... ON.

1 Answers1