>-----Original Message----- >From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On >Behalf Of Baron Schwartz >Sent: Wednesday, February 04, 2009 10:03 AM >To: Jerry Schwartz >Cc: mysql@lists.mysql.com >Subject: Re: WHERE vs. ON > >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. > [JS] Will do, as the occasion arises. I'm using 4.1.22 community edition, so I gather that some of the optimizations would not happen.
>There is no way to write a LEFT JOIN without putting the criteria into >the ON clause however. > [JS] Understood. >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 [JS] As a matter of style, I do not use comma-joins. I prefer to type some extra characters in order to make my code more accessible to the casual observer. >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. > [JS] Gotcha. I have a question about LEFT JOINs, but I'll start a separate thread for that. Thanks. >-- >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
