List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:February 4 2009 3:02pm
Subject:Re: WHERE vs. ON
View as plain text  
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.

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:
Our Services:
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