List:General Discussion« Previous MessageNext Message »
From:John Daisley Date:February 4 2009 3:40pm
Subject:Re: WHERE vs. ON
View as plain text  
Never tried it in MySQL and Im not
in a position to do so
at the moment
but in Oracle you can do a left outer join in the where
clause something
like this

SELECT t1.col1, t2.col2
FROM table1 t1, table2 t2
WHERE t1.join_col_name = t2.join_col_name(+)

Does this not work in MySQL?

Never tried it because I prefer my joins to be done in the
FROM clause
using 'JOIN.....ON....' or 'JOIN.....USING.....'

Regards
John

> 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: 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=1
>
>
> ______________________________________________
> This email has been scanned by Netintelligence
> http://www.netintelligence.com/email
>
>


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