3 Replies Latest reply: Feb 9, 2013 11:26 AM by ranit B RSS

    outer join

    pawii
      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
          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---
            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
              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.