Outer join query in Oracle using the (+) sign By Budi Raharjo Jan 04, 2006  digg!     Print     Email to Friend Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.

Outer join query in Oracle using the (+) sign This article will show you how to create outer join query in Oracle using the (+) sign rather than LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN clause, since Oracle 8 didn't know them. It is simple to do this work. Assume we have two tables (T1 and T2) with the structure is shown below: Table T1 Column Name Data Type ----------- ---------- A int R int SQL> create table T1 ( A int, R int ); SQL> insert into T1 (A, R) values (1, 1); SQL> insert into T1 (A, R) values (3, 3); SQL> select * from T1; A R ---- ---- 1 1 3 3 Table T2 Column Name Data Type ----------- ---------- B int R int SQL> create table T2 ( R int, B int ); SQL> insert into T2 (R, B) values (2, 2); SQL> insert into T2 (R, B) values (3, 4); SQL> insert into T2 (R, B) values (4, 4); SQL> select * from T2; R B ---- ---- 2 2 3 4 4 4 I. LEFT OUTER JOIN Here is the script to create left outer join query. select * from T1, T2 where T1.R = T2.R (+); That script will produce the following output: A R R B ---- ---- ---- ---- 1 1 3 3 3 4 The same result will be produced when we use the following script (in Oracle 9i) select * from T1 left outer join T2 on T1.R = T2.R; II. RIGHT OUTER JOIN Here is the script to create right outer join query. select * from T1, T2 where T1.R (+) = T2.R; That script will produce the following output: A R R B ---- ---- ---- ---- 2 2 3 3 3 4 4 4 The same result will be produced when we use the following script (in Oracle 9i) select * from T1 right outer join T2 on T1.R = T2.R; III. FULL OUTER JOIN To create full outer join using (+) sign, we need combine two queries above using UNION, like this: select * from T1, T2 where T1.R = T2.R (+); UNION select * from T1, T2 where T1.R (+) = T2.R; The output is below: A R R B ---- ---- ---- ---- 1 1 3 3 3 4 2 2 4 4 In Oracle 9i, we can do it with the following script. select * from T1 full outer join T2 on T1.R = T2.R;