Re: ANSI join syntax

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Wed, 6 Apr 2005 21:42:02 -0400
Message-ID: <100904971319.20050406214202@gennick.com>

Hello Paul,

It *can* make a difference when you move a predicate from the WHERE clause into the JOIN clause. (It surprised me too) Have a look at the following article:

http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html

So far as I've ever been able to determine, moving a predicate between WHERE and JOIN can only possibly make a difference when outer-joins are involved. In essense, if you "say" it is a join predicate, then SQL treats it like one. Again, as I admit in the article, I was caught out once myself by this behavior.

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

Wednesday, April 6, 2005, 5:06:15 PM, Paul Baumgartel (paul.baumgartel_at_gmail.com) wrote:

PB> I confess that I don't use the ANSI join syntax much (probably because
PB> I don't write much SQL any more).  My question involves the presence
PB> of non-join predicate clauses in the ON part of a join clause.  I have
PB> a developer who complains that this query gives him incorrect results
PB> (i.e., more than one row):

PB> SELECT c.company_fk, cs.store_fk
PB> FROM 
PB>   company c  
PB> LEFT JOIN store cs 
PB>        ON cs.company_fk = c.company_fk  
PB>             AND (c.effective_date IS NULL OR c.effective_date <=
PB> cs.effective_date )
PB>             AND (c.expiration_date IS NULL OR c.expiration_date >
PB> cs.effective_date )

PB> WHERE
PB> cs.store_fk = 18793

PB> It returns 8 rows, only one of which has a value (18793) for store_fk; PB> the other rows have a null store_fk.

PB> I replied, what happens when you run

PB> SELECT c.company_fk, cs.store_fk
PB> FROM 
PB>   company c  
PB> LEFT JOIN store cs 
PB>        ON cs.company_fk = c.company_fk  
PB> WHERE 
PB>   cs.store_fk = 18793
PB>             AND (c.effective_date IS NULL OR c.effective_date <=
PB> cs.effective_date )
PB>             AND (c.expiration_date IS NULL OR c.expiration_date >
PB> cs.effective_date )

PB> where the non-join predicates are where they belong. That query PB> returns one row, as expected.

PB> How, then, does Oracle evaluate the additional predicate clauses in PB> the ON part of the join?

PB> Thanks.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 06 2005 - 21:45:55 CDT