Re: Another Outer Join Question

I think you have a point in your analysis of the SQL statement. However, y.y_key is your primary key on table y otherwise you wouldn;t be joining to it. As a oprimary key this shouldn;t ever be NULL so your predicate AND y.y_key IS NULL should only ever be true if it's a case of the no corresponding Y records for your X record.

If you're still not satisfied then write the SQL in another way.

You want all records in X with no corresponding record in Y. Try something like..

SELECT x.rowid

   FROM x
where not exists (select 1 from Y where Y.y_key = x.y_key);

It may not be as fast (Actually, I haven't tried it) but it more matches your original spec.

Have fun.

--
Alan D. Mills

Andrew Forsyth wrote in message
<#TByMzUH#GA.287_at_nih2naaa.prod2.compuserve.com>...
>Hi all,
>
>
>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.
>
Received on Wed Dec 02 1998 - 05:13:48 CST