3 Replies Latest reply on Feb 9, 2013 5:26 PM by ranit B

# outer join

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