when you JOIN on:

( e.deptno = d.deptno and d.deptno is null )


it is like you are saying:

for x in ( select * from emp )
loop

   open c for 
   select * 
     from dept 
    where X.DEPTNO = dept.deptno 
      AND dept.deptno is null;
   -- the above cursor will NEVER fetch a row, never
   -- the ON join clause you gave prevents it from
   -- seeing any data since dept.deptno would have to simultaneously
   -- be equal to X.DEPTNO and it  would have to be NULL.  that will
   -- never ever happen

   -- that means that EVERY record in EMP, by definition, will be output
   -- with null's for every DEPT attribute.

   fetch C into some_record;
   if c%notfound 
   then
       output EMP without any DEPT information (outer join)
   else
       loop
          output EMP with some_record
          fetch c into some_record;
          exit when c%notfound;
       end loop
   end if;
end loop;

when you join on

select e.*
From emp e left join dept d
ON ( e.deptno = d.deptno )
where d.deptno is null


that is like saying:

for x in (select * from emp)
loop
   open c for
   select * 
     from dept
    where X.DEPTNO = dept.deptno;

   fetch C into some_record;
   if c%notfound 
   then
       output EMP without any DEPT information (outer join)
       -- this never happens since EMP.DEPTNO always has a value 
       -- and EMP.DEPTNO is a foreign key to DEPT.DEPTNO
   else
       loop
          if ( some_record.deptno is null )
          then
               output EMP with some_record
               -- this never happens since every emp.deptno is in dept.deptno
          end if;
          fetch c into some_record;
          exit when c%notfound;
       end loop
   end if;
end loop;

the ON clause is used to JOIN (it is used to perform the natural or outer join) AND THEN the predicate is applied to those rows.


when you outer join using an ON condition that is never true, the table that is driving the join will just be output "as is".

consider:

ops$tkyte%ORA11GR2> select emp.empno, dual.*
  2    from emp left outer join dual on (1=0)
  3  /

     EMPNO D
---------- -
      7788
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.

that query is semantically equivalent to "select * from emp". since the ON condition (the join condition) can never be true - EMP will just be output.



The alternative for:

select * from emp where deptno not in (select deptno from dept)


would/could be (using the fact that DEPTNO is a primary key of DEPT and hence, DEPT.DEPTNO *cannot be null* - if dept.deptno was not a primary key - was allowed to have nulls - this would NOT be an equivalent query)

select emp.*
  from emp left outer join dept on (emp.deptno = dept.deptno)
 where dept.deptno is NULL;

if dept.deptno is allowed to be null, it would be:

select emp.*
  from emp left outer join dept on (emp.deptno = dept.deptno)
 where dept.deptno is NULL
   and not exists (select null from dept where deptno is null);