Re: WHERE vs. ON

2009-02-04 Thread Claudio Nanni
2009/2/4 Baron Schwartz > On Tue, Feb 3, 2009 at 12:24 PM, Jerry Schwartz > 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 optimi

Re: WHERE vs. ON

2009-02-04 Thread Martijn Tonies
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? Luckily, it

RE: WHERE vs. ON

2009-02-04 Thread Jerry Schwartz
>-Original Message- >From: John Daisley [mailto:john.dais...@mypostoffice.co.uk] >Sent: Wednesday, February 04, 2009 10:41 AM >To: mysql@lists.mysql.com >Cc: Jerry Schwartz >Subject: Re: WHERE vs. ON > >Never tried it in MySQL and Im not >in a position to d

Re: WHERE vs. ON

2009-02-04 Thread John Daisley
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 be

RE: WHERE vs. ON

2009-02-04 Thread Jerry Schwartz
>-Original Message- >From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On >Behalf Of Baron Schwartz >Sent: Wednesday, February 04, 2009 10:03 AM >To: Jerry Schwartz >Cc: mysql@lists.mysql.com >Subject: Re: WHERE vs. ON > >On Tue, Feb 3, 2009

Re: WHERE vs. ON

2009-02-04 Thread Baron Schwartz
On Tue, Feb 3, 2009 at 12:24 PM, Jerry Schwartz 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

Re: WHERE vs. ON

2009-02-03 Thread Rob Wultsch
On Tue, Feb 3, 2009 at 1:54 PM, Jerry Schwartz wrote: > From: Martin Gainty [mailto:mgai...@hotmail.com] > Sent: Tuesday, February 03, 2009 1:03 PM > To: Jerry Schwartz; mysql@lists.mysql.com > Subject: RE: WHERE vs. ON > ON condition uses the same columnname from both source an

RE: WHERE vs. ON

2009-02-03 Thread Jerry Schwartz
From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: Tuesday, February 03, 2009 1:03 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: RE: WHERE vs. ON ON condition uses the same columnname from both source and target tables whereas any column expressions can go in the WHERE

Re: WHERE vs. ON

2009-02-03 Thread Perrin Harkins
On Tue, Feb 3, 2009 at 12:24 PM, Jerry Schwartz 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

RE: WHERE vs. ON

2009-02-03 Thread Martin Gainty
ON condition uses the same columnname from both source and target tables whereas any column expressions can go in the WHERE clause... Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official