外连接
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
-- out join sequenct : sub<emp<dept
/*1 .考虑结果 sub(+) = emp => Collection1 => 结果集第一,二列有NULL值
2.考虑 Collection1(+) =dept =>Collection2*/
select *
from (
select deptno, max(sal) max from emp group by deptno) sub,
emp,
dept
where sub.deptno(+) = emp.deptno
and sub.max(+) = emp.sal
and dept.deptno = emp.deptno(+)
/* group by dept.deptno;*/
------------
-- 没有层级关系,dept主表=> 结果集第一,二列没有NULL值
select *
from (
select deptno, max(sal) max from emp group by deptno) sub,
emp,
dept
where dept.deptno = sub.deptno(+)
and dept.deptno = emp.deptno(+)
-- and sub.max = emp.sal -->这样写不好.而且对于dept=40来说,sub.sal is null ,emp.sal is null .in orcale null !=null
---------------------------------------------------------------
--得到每个DEPT 中SAL最高的人有几个
select dept.deptno,count(sub.deptno)
from (
select deptno, max(sal) max from emp group by deptno) sub,
emp,
dept
where sub.deptno(+) = emp.deptno
and sub.max(+) = emp.sal
and dept.deptno = emp.deptno(+)
group by dept.deptno ;
--这样写最清楚
select dept.deptno, nvl(count, 0) count
from dept,
(select emp.deptno, count(emp.deptno) count, emp.sal
from (
select deptno, max(sal) max from emp group by deptno) sub,
emp
where sub.deptno = emp.deptno
and sub.max = emp.sal
group by emp.deptno, emp.sal) sub2
where sub2.deptno(+) = dept.deptno;
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
posted on 2009-05-02 20:15 dolphin_bobo 阅读(128) 评论(0) 编辑 收藏 举报