In mysql which inner join sql is most effective and best?

Question

In mysql which inner join sql is most effective and best? 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
1.

select t01.uname, t02.deptname
from   user t01,  department t02
where  t01.deptid = t02.deptid
and    t01.uid    = '001'

2.

select t01.uname, t02.deptname
from   user t01,  department t02
where  t01.uid    = '001' 
and    t01.deptid = t02.deptid

3.

select t01.uname, t02.deptname
from   user t01 inner join department t02 on t01.deptid = t02.deptid
                                         and t01.uid = '001'

4.

select t01.uname, t02.deptname
from   user t01 inner join department t02 on t01.deptid = t02.deptid
where  t01.uid = '001'

My mysql is 5.1

Answer 1

All of those are functionally equivalent. Even the separation between WHERE clause and JOIN condition will not change the results when working entirely with INNER joins (it can matter with OUTER joins). Additionally, all of those should work out into the exact same query plan (effectively zero performance difference). The order in which you include items does not matter. The query engine is free to optimize as it sees best fit within the functional specification of the query. Even when you identify specific behavior with regards to order, you shouldn't count on it. The specification allows for tomorrow's patch to change today's behavior in this area. Remember: the whole point of SQL is to be set-based and declarative: you tell the database what you want it to do, not how you want it to do it.

Now that correctness and performance are out of the way, we're down to matters of style: things like programmer productivity and readability/maintainability of the code. In that regard, option #4 in that list is by far the best choice, with #3 the next best, especially as you start to get into more complicated queries. Just don't use the A,B syntax anymore; it's been obsolete since the 1992 version of the SQL standard. Always write out the full INNER JOIN (or LEFT JOIN/RIGHT JOIN/CROSS JOIN etc).

All that said, while order does (or, at least, should) not matter to performance, I do find it helpful when I'm writing SQL to use a convention in my approach that does dictate the order. This helps me identify errors or false assumptions later when debugging and troubleshooting. This general guide that I try to follow is to behave as if the order does matter, and then with that in mind try to keep the working set of memory needed by the database to fulfill the query as small as possible for as long as possible: start with smaller tables first and then join to the larger; when considering table size, take into account conditions in the WHERE clause that match up with an index; prefer the inner joins before outer when you have the choice; list join conditions to favor indexes (especially primary/clustered keys) first, and other conditions on the join second.