This discussion is archived
3 Replies Latest reply: Feb 9, 2013 9:26 AM by ranit B RSS

outer join

pawii Newbie
Currently Being Moderated
what is the big deal if we place (+) in the wrong side in case of null values
if there are two tables outer joined by a column having null value then wht will be the effect of putting (+) in
table1.column=table2.column(+)
table1.column(+)=table2.column
table 1 has got the null value
if i put the (+) on table1 side then the null value is multiplied by each row of table 2 why??
  • 1. Re: outer join
    sb92075 Guru
    Currently Being Moderated
    987018 wrote:
    what is the big deal if we place (+) in the wrong side in case of null values
    if there are two tables outer joined by a column having null value then wht will be the effect of putting (+) in
    table1.column=table2.column(+)
    table1.column(+)=table2.column
    table 1 has got the null value
    if i put the (+) on table1 side then the null value is multiplied by each row of table 2 why??
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ


    http://www.lmgtfy.com/?q=oracle+outer+join
  • 2. Re: outer join
    ---Brodyaga--- Explorer
    Currently Being Moderated
    Hi

    In case TABLE1 LEFT JOIN TABLE2 (TABLE2.COLUMN(+)) result table would be contains ALL rows from TABLE1 and those rows from TABLE2, that satisfied condition ON, plus null rows, that not satisfied condition ON.
    In case TABLE1 RIGHT JOIN TABLE2 (TABLE1.COLUMN(+)) result table would be contains ALL rows from TABLE2 and those rows from TABLE1, that satisfied condition ON, plus null rows, that not satisfied condition ON.
  • 3. Re: outer join
    ranit B Expert
    Currently Being Moderated
    I often get confused with the OUTER JOINS and whenever in doubt I prefer work-outs.

    Please check this (if helps) -
    ranit@XE11GR2>> select *
      2  from join_1;
    
             A B              C
    ---------- ----- ----------
             1 ranit          1
             2 sam            2
             3 mann           1
             4 rath           2
             5 patel          1
    
    Elapsed: 00:00:00.01
    ranit@XE11GR2>> select *
      2  from join_2;
    
             C D
    ---------- -------
             1 asia
             3 europe
    
    Elapsed: 00:00:00.01
    ranit@XE11GR2>> 
    ranit@XE11GR2>> 
    ranit@XE11GR2>> select *
      2  from
      3  join_1,join_2
      4  where
      5  join_1.c = join_2.c;
    
             A B              C          C D
    ---------- ----- ---------- ---------- -------
             5 patel          1          1 asia
             3 mann           1          1 asia
             1 ranit          1          1 asia
    
    Elapsed: 00:00:00.00
    ranit@XE11GR2>> select *
      2  from
      3  join_1,join_2
      4  where
      5  join_1.c = join_2.c(+);
    
             A B              C          C D
    ---------- ----- ---------- ---------- -------
             5 patel          1          1 asia
             3 mann           1          1 asia
             1 ranit          1          1 asia
             4 rath           2
             2 sam            2
    
    Elapsed: 00:00:00.01
    ranit@XE11GR2>> select *
      2  from
      3  join_1,join_2
      4  where
      5  join_1.c(+) = join_2.c;
    
             A B              C          C D
    ---------- ----- ---------- ---------- -------
             1 ranit          1          1 asia
             3 mann           1          1 asia
             5 patel          1          1 asia
                                         3 europe
    
    Elapsed: 00:00:00.01
    ranit@XE11GR2>> select *
      2  from
      3  join_1 LEFT OUTER JOIN join_2
      4  on
      5       join_1.c = join_2.c;
    
             A B              C          C D
    ---------- ----- ---------- ---------- -------
             5 patel          1          1 asia
             3 mann           1          1 asia
             1 ranit          1          1 asia
             4 rath           2
             2 sam            2
    
    Elapsed: 00:00:00.01
    ranit@XE11GR2>> select *
      2  from
      3  join_2 LEFT OUTER JOIN join_1
      4  on
      5       join_1.c = join_2.c;
    
             C D                A B              C
    ---------- ------- ---------- ----- ----------
             1 asia             1 ranit          1
             1 asia             3 mann           1
             1 asia             5 patel          1
             3 europe
    
    Elapsed: 00:00:00.00
    ranit@XE11GR2>> select *
      2  from
      3  join_1 RIGHT OUTER JOIN join_2
      4  on
      5       join_1.c = join_2.c;
    
             A B              C          C D
    ---------- ----- ---------- ---------- -------
             1 ranit          1          1 asia
             3 mann           1          1 asia
             5 patel          1          1 asia
                                         3 europe
    
    Elapsed: 00:00:00.00
    ranit@XE11GR2>> select *
      2  from
      3  join_2 RIGHT OUTER JOIN join_1
      4  on
      5       join_1.c = join_2.c;
    
             C D                A B              C
    ---------- ------- ---------- ----- ----------
             1 asia             5 patel          1
             1 asia             3 mann           1
             1 asia             1 ranit          1
                                4 rath           2
                                2 sam            2
    
    Elapsed: 00:00:00.01
    Please let me know if you have any concerns. HTH.
    Ranit B.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points