ANSI join syntax

From: Paul Baumgartel <paul.baumgartel_at_gmail.com>
Date: Wed, 6 Apr 2005 17:06:15 -0400
Message-ID: <f8c47710504061406389a020@mail.gmail.com>

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

SELECT c.company_fk, cs.store_fk
FROM
  company c
LEFT JOIN store cs

       ON cs.company_fk = c.company_fk  
            AND (c.effective_date IS NULL OR c.effective_date <=
cs.effective_date )
            AND (c.expiration_date IS NULL OR c.expiration_date >

cs.effective_date )
WHERE
  cs.store_fk = 18793

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

I replied, what happens when you run

SELECT c.company_fk, cs.store_fk
FROM
  company c
LEFT JOIN store cs

       ON cs.company_fk = c.company_fk WHERE
  cs.store_fk = 18793

            AND (c.effective_date IS NULL OR c.effective_date <= cs.effective_date )

            AND (c.expiration_date IS NULL OR c.expiration_date > cs.effective_date )

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

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

Thanks.

-- 
Paul Baumgartel
paul.baumgartel_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 06 2005 - 17:12:32 CDT