Re: Outer joins on constants (or variables)

From: L120bj <l120bj_at_aol.com>
Date: 1997/03/06
Message-ID: <19970306004500.TAA17693@ladder01.news.aol.com>#1/1

I think it is to do with the fact that ORACLE is trying to join rows from tables. When outer joining to a column in a table, then there is a row on 1 table that may not exist on another. However, when outer joining to constant/variable then there is no row on the database to join to. Does that seem logical to you ? I've just come back from a night on the ale, so although it seems logical to me, my logic may be somewhat addled by the alcohol.

Rob

---

---

Subject: Outer joins on constants (or variables) From: John Higley <johnhi_at_biker.bv.tek.com> Date: Wed, 05 Mar 1997 11:20:09 -0800
Message-ID: <331DC769.41C67EA6_at_biker.bv.tek.com>

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 Thu Mar 06 1997 - 00:00:00 CST