Re: ANSI join syntax
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: paul.baumgartel@xxxxxxxxx
- Date: Wed, 06 Apr 2005 23:56:47 +0200
Paul,
What the optimizer does behind your back, I don't know. But I guess
that it is as least trying to respect the spirit of the ANSI join, which
is, as far as I understand it, to make a clear distinction between what
the conditions which allow to join two tables together and the
conditions which are supposed to filter the resulting output. You can of
course sometimes consider that you are joining to a subset of another
table, and push some 'local' filtering conditions to the JOIN clause -
somehow, it is pretty close to inline views. But it doesn't work with IS
NULL conditions on left (outer) joins, because NULL columns pop out of
nowhere precisely as a result of the join.
No great fan of ANSI joins either (I sympathize with the intent,
though). The only quality I have found to them so far is that they make
building complex queries dynamically *slightly* easier.
Stephane Faroult
Paul Baumgartel wrote:
>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.
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
