On Tue, Feb 3, 2009 at 12:24 PM, Jerry Schwartz <jschwa...@the-infoshop.com> wrote: > Somebody, I think it was somebody from MySQL, said that you should never put > anything into a WHERE clause that could be put into the ON clause of a JOIN. > My guess is that this helps with the optimization, but it seems > counter-intuitive to me. I've never followed that advice, but I'm starting
There are two things here -- one is coding style. If you're using all [INNER] JOIN then it doesn't matter, they are equivalent and the optimizer is smart enough to know that a condition holding two columns equal is the same whether it's in the WHERE or the ON. Take a look at the output of EXPLAIN EXTENDED... SHOW WARNINGS to see how the optimizer rewrites the query. There is no way to write a LEFT JOIN without putting the criteria into the ON clause however. As a matter of style I do not like comma-joins with the criteria in the WHERE clause. I want ON clauses to show me how two tables are related, and the WHERE clause to show me how the results are filtered thereafter. But oddly enough some people think this is horrible and they think it's much clearer to do it the reverse way! Everyone's entitled to their own wrong opinion ;-) Seriously it is just a matter of style until you get to LEFT JOIN. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
