Re: Another Outer Join Question

Andrew,

I think you will be quite safe with your outer join here. It doesn't make sense for a restriction "y.y_key is null" based on a column of the 'right hand' table to be performed 'before' a left outer join "x.y_key = y.y_key (+)", since the values of many of the columns in the result set could only be determined after the join has been performed. If any SQL optimiser were to use such a plan it could only be described as a bug in my opinion.

Regards, David.

Andrew Forsyth wrote:

> 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 Tue Dec 01 1998 - 17:38:50 CST