ANSI join syntax
- From: Paul Baumgartel <paul.baumgartel@xxxxxxxxx>
- To: "Oracle-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Apr 2005 17:06:15 -0400
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@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
