Re: ANSI join syntax

From: Jonathan Gennick <jonathan@xxxxxxxxxxx>

To: Paul Baumgartel <paul.baumgartel@xxxxxxxxx>

Date: 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.
--
http://www.freelists.org/webpage/oracle-l