Re: ANSI join syntax

Jonathan Gennick <jonathan@xxxxxxxxxxx> · Wed, 6 Apr 2005 21:42:02 -0400

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@xxxxxxxxxxx

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@xxxxxxxxxxx and 
include the word "subscribe" in either the subject or body.

Wednesday, April 6, 2005, 5:06:15 PM, Paul Baumgartel 
(paul.baumgartel@xxxxxxxxx) 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.

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

Other related posts: