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 > <jschwa...@the-infoshop.com> 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=john.dais...@butterflysystems.co.uk > > > ______________________________________________ > This email has been scanned by Netintelligence > http://www.netintelligence.com/email > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
