Query conditions

Hi all. I've got a greenhorn question but I didn't find the right
keywords to get Google to answer it for me.

Is it more efficient to put conditions in a JOIN instead of sticking
them all at the end in the WHERE clause, or is that just a matter of
preference? Putting conditions in the JOIN It seems, to me, to at least
make the query easier to read. I was just curious if there were
performance gains as well.

Example:

SELECT FROM table1
JOIN table2 ON this > that
WHERE that = '5';

vs

SELECT FROM table1
JOIN table2 WHERE this > that AND that = '5';

Thanks!
G

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Query conditions

It's about syntax and clarity, both for the reader and to the query
optimizer. The JOIN conditions belong in the ON sub-statement
precisely because they are the predicate for the join and serve to
grow the data set .. the WHERE query is supposed to specify limiting
conditions on that superset.

When I was a newd, I recall sticking my conditions in the WHERE
clauses because it seemed to work most of the time, but I would get
these occasional glitches where the result did unexpected things.
Since I learned the conventions properly, that almost never happens.

- md

On Fri, Sep 10, 2010 at 9:33 AM, george larson
wrote:
> Hi all. =A0I've got a greenhorn question but I didn't find the right
> keywords to get Google to answer it for me.
>
> Is it more efficient to put conditions in a JOIN instead of sticking
> them all at the end in the WHERE clause, or is that just a matter of
> preference? =A0Putting conditions in the JOIN It seems, to me, to at leas=
t
> make the query easier to read. I was just curious if there were
> performance gains as well.
>
> Example:
>
> SELECT FROM table1
> JOIN table2 ON this > that
> WHERE that =3D '5';
>
> vs
>
> SELECT FROM table1
> JOIN table2 WHERE this > that AND that =3D '5';
>
> Thanks!
> G
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg