"On" and "Where" Clause - Difference

Not, entirely learnt newly today but revisited some good old stuffs. Its about the difference between "on" and  "when" clauses in queries.


Let's say we have two table's "T1" and "T2"


create table t1(x number,y number);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(null,3);


create table t2(x number,y number);
insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(null,3);
commit;


rajesh@ORA11GR2> select t1.*
  2  from t1 left outer join t2
  3  on t1.x = t2.x
  4  and t1.x is not null
  5  and t2.x is not null;
         X          Y
---------- ----------
         1          1
         2          2
                    3


3 rows selected.


Elapsed: 00:00:00.01
rajesh@ORA11GR2>


Now, rewriting this query using Oracle native sql approch. It return different results.


rajesh@ORA11GR2> select t1.*
  2  from t1 , t2
  3  where  t1.x = t2.x (+)
  4  and t1.x is not null
  5  and t2.x is not null;


         X          Y
---------- ----------
         1          1
         2          2


2 rows selected.


Elapsed: 00:00:00.00
rajesh@ORA11GR2>


The problem is both the queries don't make the logic "the same", there is a huge difference between "on" and "where" 


Query-1 is executed mostly like this.


rajesh@ORA11GR2> select t1.*
  2  from t1 left outer join t2
  3  on (
  4      t1.x = t2.x
  5      and t1.x is not null
  6      and t2.x is not null
  7  );
         X          Y
---------- ----------
         1          1
         2          2
                    3


3 rows selected.


Elapsed: 00:00:00.01
rajesh@ORA11GR2>


So, the correct way of rewriting this query using Oracle native sql approch is to make joins in "ON" clause and filter the results sets using "Where" clause.


rajesh@ORA11GR2> select t1.*
  2  from t1 left outer join t2
  3  on t1.x = t2.x
  4  where t1.x is not null
  5  and t2.x is not null;
         X          Y
---------- ----------
         1          1
         2          2


2 rows selected.


Elapsed: 00:00:00.01
rajesh@ORA11GR2>


"on"     - restrict the row to be selected from DATA SOURCE for Join to be performed.


"when" - restrict the row to be selected from  RESULT SETS to display at client terminal.