partition outer join

partition outer join   ---

对于员工100，101分别和部门表进行outer join 。此时用到right outer join，对于employee表中不匹配的记录，employee_id字段不显示为null，而是显示实际的employee_id的值，而其它的字段则显示为空。

select e.employee_id, e.department_id, d.department_id

  from hr.employees e partition by(employee_id)

 right outer join departments d on e.department_id = d.department_id

 where e.employee_id in (100,101)

 and d.department_id in (10,20,90)

 

结果为

EMPLOYEE_ID	DEPARTMENT_ID	DEPARTMENT_ID
100		10
100		20
100	90	90
101		10
101		20
101	90	90

 

相当于以下的语句

select nvl(e.employee_id, 100), e.department_id, d.department_id
  from (select * from hr.employees where employee_id = 100) e
 right outer join departments d on e.department_id = d.department_id
 where d.department_id in (10, 20, 90)
union all
select nvl(e.employee_id, 101), e.department_id, d.department_id
  from (select * from hr.employees where employee_id = 101) e
 right outer join departments d on e.department_id = d.department_id
 where d.department_id in (10, 20, 90)

 

此时的employee_id使用nvl将填充空值，employee_id = 100的过滤条件放在子查询中，若放于外面，由于是先过滤后运行function NVL，则导致以上的结果只显示两条记录，其它记录因为employee_id为空而被过滤了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30821/viewspace-442330/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30821/viewspace-442330/