Re: Another Outer Join Question

In article <#TByMzUH#GA.287_at_nih2naaa.prod2.compuserve.com>,   "Andrew Forsyth" <arf001DELETETHIS_at_yahoo.Kom> wrote:
> Hi all,
>
> Another outer join question. I have done a DejaNews search and read some
> interesting stuff but I'm still not 100% certain. If this is a perennial
> question on this ng then my apologies, and a pointer in the right direction
> would be appreciated.
>
> Consider tables X and Y. X has an opitional foreign key (Y_KEY) to table Y.
> This is not enforced by any constraint.
>
> I am looking for the rowids of X records which have invalid foreign keys to
> Y, ie they have a non-null value of Y_key and there is no corresponding
> record in Y with that value of Y_KEY.
>
> select
> x.rowid
> from
> x,y
> where
> x.y_key = y.y_key (+)
> and x.y_key is not null
> and y.y_key is null;
>
> This works, I've tried it, I've seen other examples which do the same thing,
> but I am still nervous. Can I be absolutely certain that the optimiser is
> not going to use my "y.y_key is null" criterion as a filter on y BEFORE
> doing the join? This would return rowids of all my x rows with non-null
> Y_KEYs, regardless of the validity of these fields.
>
> Am I making sense? Do you see why I am so worried? Is Oracle going to come
> up with a completely dumb plan the minute I turn my back? It would be
> entirely logical (if completely wrong from my pov) for Oracle to start with
> my last criterion and regard Y as an empty table 'cos there are no rows in Y
> where Y key is null and THEN proceed to join it (the filtered, empty Y) to
> X.
>

Yes, it will work. No you should not worry about the optimizer. By the vary definition, both factors need to be true.

Alternative ideas.

   select

         x.rowid
   from

         x
   where

         x.y_key in ( select distinct y_key from X where y_key is not null
                       minus
                      select distinct y_key from y where y_key is not null)

assuming you want to actually do something with the table x. This is a little slower, but could allow for other manipulations.

> What are the rules for precedence of expressions in such a case? (If your
> answer to this is "RTFM", then could you give me a reference of which "FM" I
> should "R" please?)
>
> I know I could push the whole query down a level into an inline view and
> check the null-ness of y.y_key in the result set of that, but the 7.3
> version I am using won't allow me to return rowids from an inline view in a
> PL/SQL cursor so this alternative approach becomes less efficient (as I
> can't access the bad rows by rowid in my procedure).
>
> Thanks in advance.
>
> ---ARF---
>
>

--
Michael Krolewski
mikkro_at_hbsi.com
All opinions are my own, and do not represent my employer.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Dec 03 1998 - 02:24:59 CST