Monday, November 30, 2015

Oracle native out join syntax learned

One more out join syntax (+) observed.

Setup

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;


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


  T1_ID T2_ID
------- ----------
  1     1
  3


Here is a new syntax I learned last week.
The output is strange.

select t1.id t1_id, t2.id t2_id
from t2,t1
where t1.id = t2.id(+)
and t2.id(+) = 1

order by t1.id;

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

2 comments:

Unknown said...

If you use ANSI syntax, the result will be obvious and clear. The reason is logically join happens before filtering (the where predicate). For inner join, the result will be the same whatever Oracle choose the execution plan (or order of applying the join and filtering condition). It can apply filter condition before join (e.g. via specific access path), at the time of join, or after join. But for outer join, the result will be different if a join condition is applied after join, at filtering stage. So here the second (+) tells Oracle, this condition is a join condition, not a filtering predicate.

Unknown said...

不明觉厉。在研究研究。呵呵。