3 Replies Latest reply: Mar 31, 2013 11:37 AM by Frank Kulash RSS

    outer join issue

    975169
      Hello,

      I would like to perform an outer join and i have this requirement i need to monitor both null values and no match values seperately.
      Sounds complicated? i'll try to explain.
      You know when you perform an outer (let's say left) join for each record (by saying record i mean the join condition) that exists in the left table that does not exists in the right table you'll get a null value for all fields in select statement (that are not in the join condition). Now, how can i assign a default value to null values from the right table? In order to distinguish between null values that are originally null values in right table and null values that are as a result of a dismatch of join condition
      for example:
      i have this table two tables:

      table a table b
      subscr_no          subscr_no     end_call_date_time
      1               1     3/1/2013 21:21
      2               2     30/5/2012 18:49
      5               5     5/4/2012 18:08
      7               1     
      3               7     30/9/2012 14:12
      6               3     19/1/2013 23:05
      4               6     19/10/2012 21:37
      9               5     
      8               4     16/5/2012 10:23
                     1     31/12/2012 7:53
                     2     
                     2     29/11/2012 3:28
                     7     
                     7     25/7/2012 0:09
                     3     29/1/2013 8:53

      let's say i'l perform a left join like this:
      select b.end_call_date_time
      from b left join a
      on (b.subscr_no=a.subscr_no)

      now, notice that the relationship between a and b is 1 to many and subscr_no 8 and 9 appear in a table do not appear in b table.
      notice that b table contain null values for end_call_date_time field.
      in the joined table, how will i know which null values from end_call_date_time are originally null in b table and wich null values are because subscr_no 8 and 9 do not appear in b table?

      thanks
        • 1. Re: outer join issue
          Christine Schnittker
          >
          how will i know which null values from end_call_date_time are originally null in b table and wich null values are because subscr_no 8 and 9 do not appear in b table?
          >

          You'll know if you check b.subscr_no - if it is null, there wasn't a match. If it is not null, you have an originally-null end date.
          //Tine
          • 2. Re: outer join issue
            user648773
            select nvl2(b.subscr_no,nvl(b.end_call_date_time,'EXISTS_BUT_NULL'),'NOT_EXISTS')
            from b left join a
            on (b.subscr_no=a.subscr_no)
            • 3. Re: outer join issue
              Frank Kulash
              Hi,
              972166 wrote:
              ... let's say i'l perform a left join like this:
              select b.end_call_date_time
              from b left join a
              on (b.subscr_no=a.subscr_no)

              now, notice that the relationship between a and b is 1 to many and subscr_no 8 and 9 appear in a table do not appear in b table.
              notice that b table contain null values for end_call_date_time field.
              in the joined table, how will i know which null values from end_call_date_time are originally null in b table and wich null values are because subscr_no 8 and 9 do not appear in b table?
              When you say
              FROM       b
              LEFT JOIN  a  ...
              Then all the columns that are supposed to come from b will really show what's in b. If the output for b.end_call_date_time is NULL, you can be certain that there is a NULL in the table. It's only values from table a that could appear as NULL for the two different reasons you mentioned.

              As the others have already said, a.subscr_no (in this example) will not be NULL if the ourput row represents a match.
              user648773 wrote:
              select nvl2(b.subscr_no,nvl(b.end_call_date_time,'EXISTS_BUT_NULL'),'NOT_EXISTS')
              This is a great idea if the outer join goes like this:
              from      a    -- not b
              left join b    -- not a
                            on (b.subscr_no=a.subscr_no)