PARTITION_RIGHTOUTER_JOIN1.htm
Objectives:

partition over

  • partition clause can be use either right or left side of the outer-join but no on both
  • partition clause can't be used with full outer join
  • can't use sub-query after partition clause is used in outer join-on

 
SQL:

select d.deptno,substr(d.loc,1,5)"loc", row_number() over (partition by d.deptno order by d.deptno)"rn",e.job, e.ename from dept d right outer join emp e on e.deptno = d.deptno where d.deptno in (10, 30)
oracle:

PostgreSQL
MySQL did not run