List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:February 4 2009 3:30pm
Subject:RE: WHERE vs. ON
View as plain text  
>-----Original Message-----
>From: baron.schwartz@stripped [mailto:baron.schwartz@stripped] On
>Behalf Of Baron Schwartz
>Sent: Wednesday, February 04, 2009 10:03 AM
>To: Jerry Schwartz
>Cc: mysql@stripped
>Subject: Re: WHERE vs. ON
>
>On Tue, Feb 3, 2009 at 12:24 PM, Jerry Schwartz
><jschwartz@stripped> 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



Thread
WHERE vs. ONJerry Schwartz3 Feb
  • RE: WHERE vs. ONMartin Gainty3 Feb
    • RE: WHERE vs. ONJerry Schwartz3 Feb
      • Re: WHERE vs. ONRob Wultsch3 Feb
  • Re: WHERE vs. ONPerrin Harkins3 Feb
  • Re: WHERE vs. ONBaron Schwartz4 Feb
    • RE: WHERE vs. ONJerry Schwartz4 Feb
    • Re: WHERE vs. ONJohn Daisley4 Feb
      • RE: WHERE vs. ONJerry Schwartz4 Feb
    • Re: WHERE vs. ONClaudio Nanni4 Feb
  • Re: WHERE vs. ONMartijn Tonies4 Feb