From: Claudio Nanni
Date: February 4 2009 4:54pm
Subject: Re: WHERE vs. ON
List-Archive: http://lists.mysql.com/mysql/216160
Message-Id: <53bcf3a60902040854n3ab938e3ydef57a14cd1ec3ba@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=001636c5a3d462261004621aa3ba

--001636c5a3d462261004621aa3ba
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

2009/2/4 Baron Schwartz <baron@stripped>

> 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.


HOLY WORDS!
LOGIC MATTERS!!!!

DEFINITELY: The ON clause is for joining tables, WHERE is for filtering.

If we lose the logics and set theory behind SQL we lose the way.


I really dont understand what this is supposed to mean:
----------------------------------------------------------------------------------------------
SELECT prod.prod_num, prod.prod_title, prod.prod_samp_doc

   -> FROM pub JOIN prod ON pub.pub_id = prod.pub_id

   -> AND pub.pub_code = 'dc'

   -> WHERE prod.prod_discont = 0

   -> AND prod.prod_samp_doc IS NOT NULL

   -> ORDER BY prod.prod_num\G
----------------------------------------------------------------------------------------------

I am quite surprised that MySQL allows to put a condition in the JOIN clause
that can refer to a constant value



Cheers
Claudio


 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=claudio.nanni@stripped
>
>

--001636c5a3d462261004621aa3ba--