Wednesday, July 13, 2016

Join 3 tables - Oracle native outer join v.s. ANSI outer join

select t1.id t1_id, t2.id t2_id, t3.id t3_id
from t1,t2,t3
where t1.id = t2.id(+)
and t2.id = t3.id(+)
--order by 1
;

     T1_ID      T2_ID      T3_ID
---------- ---------- ----------
         1          1          1
         3                      
         2          2           


select t1.id t1_id, t2.id t2_id, t3.id t3_id
from t1
left join t2 on t1.id = t2.id
left join t3 on t3.id = t2.id
--order by 1
;

     T1_ID      T2_ID      T3_ID
---------- ---------- ----------
         1          1          1
         3                      
         2          2           

Observation: They return same result.


Fyi,  init testing data.

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1 (id number);
create table t2 (id number);
create table t3 (id number);
insert into t1(id) select rownum from dual connect by level <= 3;
insert into t2(id) select rownum from dual connect by level <= 2;
insert into t3(id) select rownum from dual connect by level <= 1;
commit;