Re: Outer joins on constants (or variables)

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/03/05
Message-ID: <331E0069.79B2@iol.ie>#1/1

In the statement below there are no joins since no column value is compared with a column value from any other table. Therefore the question of outer joins does not arise,

If the statement were:
...
where j1.qaz = any_value
and j2.qaz(+) = j1.qaz
and j3.qaz(+) = j2.qaz
then the statement works as expected i.e. (in pseudo-PL/SQL!)
if j1.qaz = any_value then

   return j1.columns;
   if j2.qaz = j1.jaz then

      return j2.columns;
      if j3.qaz = j2.qaz then
         return j3.columns;
      else
         return j3.null;
      end if;
   else
      return j2.null;

   end if;
else

   return no rows;
end if;

Chrysalis.

John Higley wrote:
>
> Hello,
> I have an interesting question regarding outer joins -
>
> Is it true that outer joins don't work on constants or variables?
>
> I am running Oracle 7.1.6.2 and have the following query:
>
> 1 select j1.qaz,a,b,c
> 2 from j1,j2,j3
> 3 where j1.qaz = 3
> 4 and j2.qaz(+) = 3
> 5 and j3.qaz(+) = 3
>
> Where the constant 3 is replaced by a bind variable. Since 3 does not
> exist in all three tables, I get "no rows selected", instead of null
> in the table that does not contain 3.
>
> Here is my data:
>
> J1 -
> QAZ A
> ---------- ----------
> 1 1
> 2 1
> 3 1
> 4 1
>
> J2 -
> QAZ B
> ---------- ----------
> 3 2
> 4 2
>
> J3 -
> QAZ C
> ---------- ----------
> 2 3
> 4 3
>
> If I change lines 4,5 to read ...(+) = j1.qaz then it works as
> expected.
>
> Another side note: As long as any column is outer joined, other
> columns can be compared to nulls - ex: add j3.c(+) = 3 to the where
> clause.
>
> Any thoughts?
> John Higley
> Tektronix, Inc.
> john.m.higley_at_tek.com
  Received on Wed Mar 05 1997 - 00:00:00 CST