In mysql, which sql inner join is the most efficient and best?
In mysql, which sql inner join is the most efficient 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
source to share
They are all functionally equivalent. Even the separation between the WHERE clause and the JOIN clause does not change the results when fully working with INNER joins (this can make a difference when connecting OUTERs). In addition, they should all work in the same query plan (in fact, with zero performance difference). The order in which you include items doesn't matter. The query engine can be optimized as it is best suited to a functional query specification. Even if you define specific ordering behavior, you shouldn't count on it. The specification allows 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, not how you want to do it.
Now that correctness and performance have gone astray, we tackle style issues such as programmer productivity and code readability / maintainability. In this regard, option # 4 on this list is actually the best choice, and # 3 is the best, especially when you start tackling more complex queries. Just don't use syntax A,B
; it has been deprecated since the 1992 version of the SQL standard. Always write out complete INNER JOIN
(or LEFT JOIN
/ RIGHT JOIN
/ CROSS JOIN
etc.).
All that being said, while ordering (or at least should) is irrelevant to performance, I find it helpful when I write SQL to use a convention in my approach that the ordering dictates. This helps me to later identify errors or false assumptions when debugging and troubleshooting. This general guideline that I try to follow is to behave as if order matters, and then with that in mind, try to keep the working set of memory the database needs to run the query as little as possible for as long as possible: first start with smaller tables and then join the larger one; When considering table size, consider the conditions in the WHERE clause that match the index; prefer internal connections over external ones when you have a choice;(primarily primary / cluster keys) as well as other conditions for the second join.
source to share