《SQL CookBook 》笔记-第三章-多表查询
3.1 叠加两个行集
问题：
你想返回保存在多个表中的数据
解决方案：
联合查询，使用union all合并多个表中的行
select ename as ename_and_dname,deptno
from EMP
where deptno=10
union all
select '----------',null
from T1
union all
select dname,deptno
from DEPT
结果：
ename_and_dname deptno
--------------- -----------
CLARK 10
KING 10
MILLER 10
---------- NULL
ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40
(8 行受影响)
【注意】
-
多个select查询使用
union all连接，查询的列的结果放在一个结果集中，但是有一点要注意的是，每一个select的的列数必须相同，且每一个select查询的每一列的数据类型必须匹配 。 -
如果每一个select查询的结果会有重复的行，那么可以使用
union来连接每一个查询。
具体看下例：
使用union all
select deptno
from EMP
union all
select deptno
from DEPT
结果：
deptno
-----------
20
30
30
20
30
30
10
20
10
30
20
30
20
10
10
20
30
40
(18 行受影响)
使用union
select deptno
from emp
union
select deptno
from dept
结果：
deptno
-----------
10
20
30
40
(4 行受影响)
大体而言，使用union就相当于针对union all的输出结果在执行一次distinct操作
如下：
select distinct deptno
from
(
select deptno
from emp
union all
select deptno
from dept
)
关于优化，除非有必要，否则不要在查询中使用 DISTINCT 操作，同样的规则也适用于 UNION 。除非有
必要，否则不要用 UNION 代替 UNION ALL
3.2 合并相关行
问题：
如果数据存储在多个表中，怎样用一条 SELECT 语句就检索出数据呢？
例如：
你想显示部门编号为 10 的全部员工的名字及其部门所在地，但这些数据分别存储在两个表里
解决方案：
使用连接。
联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
select e.ename,d.loc
from EMP as e,DEPT as d
where e.deptno=d.deptno and e.deptno=10
结果：
| ename | loc |
|---|---|
| CLARK | NEW YORK |
| KING | NEW YORK |
| MILLER | NEW YORK |
【说明】
- 首先关于连接，必须要理解什么是关系数据库。
关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。
连接查询就是查询多个表中的数据然后一起返回。
- 使用表的连接查询，为了防止产生歧义，就要使用完全限定名，即：表名.列名。
需要注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。
此处我们使用的是表别名来简写，在from子句中我们在带查询的表后使用as写别名，其中as可以省略。
因为Oracle不需要使用as，所以我们以后也就默认省略as。
- 如果没有 WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起（这就是笛卡尔积）。
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
【说明】
内连接又称为等值连接(equal join),他是基于两个表之间的某列相等来做连接。
上面的例子就是内连接，但是那称为隐式的内连接，
我们也可以使用显示的内连接
显示的内连接使用inner join连接两个表，使用on子句做连接条件。
select e.ename,d.loc
from EMP as e inner join DEPT as d
on e.deptno=d.deptno and e.deptno=10
【注意】关于SQL优化：不要联结不必要表。联结的表越多，性能下降越厉害。
3.3 查找两个表中相同的行
问题:
你想找出两个表中相同的行
create view V
as
select ename,job,sal
from emp
where job = 'CLERK'
select * from V
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
视图 V 只包含职位是 CLERK 的员工，但并没有显示 EMP 表中所有可能的列。你想从 EMP 表获取与视图 V 相匹配的全部员工的 EMPNO 、 ENAME 、 JOB 、 SAL 和 DEPTNO ，并且希望得到如下所示的结果集:
EMPNO ENAME JOB SAL DEPTNO
-------- ---------- --------- ---------- ----------
7369 SMITH CLERK 800 20
7876 ADAMS CLERK 1100 20
7900 JAMES CLERK 950 30
7934 MILLER CLERK 1300 10
解决方案:使用连接查询
select e.empno,e.ename,e.job,e.sal,e.deptno
from emp e, V
where e.ename = v.ename
and e.job = v.job
and e.sal = v.sal
也可以使用join写法
select e.empno,e.ename,e.job,e.sal,e.deptno
from emp e join V
on
( e.ename = v.ename
and e.job = v.job
and e.sal = v.sal
)
3.4 查找只存在于一个表中的数据
问题:
你希望从一个表（可以称之为源表）里找出那些在某个目标表里不存在的值。
例如：
你想找出在 DEPT 表中存在而在 EMP 表里却不存在的部门编号（如果有的话）。
解决方案：
使用子查询得到 EMP 表中所有的 DEPTNO ，并将该结果传入外层查询，然后外层查询会检索DEPT 表，找出没有出现在子查询结果里的 DEPTNO 值。
select deptno from DEPT where deptno not in (select deptno from EMP)
结果：
| deptno |
|---|
| 40 |
【注意】如果子查询select deptno from EMP 的返回值的中有NUll值，就会复杂了。
因为IN 和NOT IN 本质上是 OR 运算，由于 Null 值参与 OR 逻辑运算的方式不同， IN 和 NOT IN 将会产生不同的结果。
考虑以下分别使用 IN 和 OR 的例子
其实我们知道DEPT表中的deptno列中的值有10，没有50和null
select deptno
from dept
where deptno in ( 10,50,null )
结果：
| DEPTNO |
|---|
| 10 |
select deptno
from dept
where (deptno=10 or deptno=50 or deptno=null)
结果：
| DEPTNO |
|---|
| 10 |
再来看看使用 NOT IN 和 NOT OR 的例子:
select deptno
from dept
where deptno not in ( 10,50,null )
结果：
无
select deptno
from dept
where not (deptno=10 or deptno=50 or deptno=null)
结果：
无
其中：
in ( 10,50,null )等价于(deptno=10 or deptno=50 or deptno=null)
【分析】
and的真值表
| true | flase | null | |
|---|---|---|---|
| true | true | flase | null |
| flase | flase | flase | flase |
| null | null | flase | null |
or的真值表
| true | flase | null | |
|---|---|---|---|
| true | true | true | true |
| flase | true | flase | null |
| null | true | null | null |
not的真值表
| true | flase | null |
|---|---|---|
| flase | true | null |
切记：在 SQL 中， TRUE or NULL 的运算结果是 TRUE ，但 FALSE or NULL 的运算结果却是 Null
必须谨记，当使用 IN 谓词以及当执行 OR 逻辑运算的时候，你要想到是否会涉及 Null 值
【注意】
为了避免 NOT IN 和 Null 值带来的问题，需要结合使用 NOT EXISTS 和关联子查询。关联子查询指的是外层查询执行后获得的结果集会被内层子查询引用。下面的例子给出了一个免受 Null 值影响的替代方案
回到“问题”--你想找出在 DEPT 表中存在而在 EMP 表里却不存在的部门编号（如果有的话）
select d.demptno
from DEPT d
where not exists
(
select null
from EMP e
where e.deptno=d.deptno
)
【分析】
- 如果子查询有结果返回给外层查询，那么 EXISTS (...) 的评估结果是 TRUE ，这样 NOT EXISTS (...) 就是 FALSE ，如此一来，外层查询就会舍弃当前行。
- 如果子查询没有返回任何结果，那么 NOT EXISTS (...) 的评估结果是 TRUE ，由此外层查询就会返回当前行（因为它是一个不存在于 EMP 表中的部门编号）。
- 把 EXISTS/NOT EXISTS 和关联子查询一起使用时， SELECT 列表里的项目并不重要，因此我在这个例子中用了 SELECT NULL
3.5 从一个表检索与另一个表不相关的行
问题：
两个表有相同的键，你想在一个表里查找与另一个表不相匹配的行。
例如：
你想找出哪些部门没有员工。
分析：
你发现好像和 3.3 查找只存在于一个表中的数据 是一样的，只要修改为
select d.*
from dept d
where not exists
(
select null
from emp e
where d.deptno = e.deptno
)
当然我们还有其他方法：使用外连接
select d.*
from dept d left outer join emp e
on (d.deptno = e.deptno)
where e.deptno is null
结果如下：
DEPTNO DNAME LOC
----------- -------------- -------------
40 OPERATIONS BOSTON
(1 行受影响)
3.6 新增连接查询而不影响其他连接查询
问题:
你已经有了一个查询语句，它可以返回你想要的数据。你需要一些额外信息，但当你试图获取这些信息的时候，却丢失了原有的查询结果集中的数据。
例如:
首先，这个例子我们要补充一个员工奖金表EMP_Bonus
EMPNO RECEIVED TYPE
---------- ----------- ----------
7369 14-MAR-2005 1
7900 14-MAR-2005 2
7788 14-MAR-2005 3
你想查找所有员工的信息，包括他们所在部门的位置，以及他们收到奖金的日期。
最初，你使用如下所示的查询语句
select e.ename, d.loc
from emp e, dept d
where e.deptno=d.deptno
结果：
ENAME LOC
---------- -------------
SMITH DALLAS
ALLEN CHICAGO
WARD CHICAGO
JONES DALLAS
MARTIN CHICAGO
BLAKE CHICAGO
CLARK NEW YORK
SCOTT DALLAS
KING NEW YORK
TURNER CHICAGO
ADAMS DALLAS
JAMES CHICAGO
FORD DALLAS
MILLER NEW YORK
对于有奖金的员工，你希望把他们收到奖金的日期也添加到结果集里，但连接了 EMP_BONUS 表后得到的行数却比预期的要少，因为并非所有的员工都有奖金。
如下：
select e.ename, d.loc,eb.received
from emp e, dept d, emp_bonus eb
where e.deptno=d.deptno
and e.empno=eb.empno
ENAME LOC RECEIVED
---------- ------------- -----------
SCOTT DALLAS 14-MAR-2005
SMITH DALLAS 14-MAR-2005
JAMES CHICAGO 14-MAR-2005
但是我们想要的结果是这样的：
ename loc received
---------- ------------- -----------------------
SMITH DALLAS 2005-03-14 00:00:00.000
ALLEN CHICAGO NULL
WARD CHICAGO NULL
JONES DALLAS NULL
MARTIN CHICAGO NULL
BLAKE CHICAGO NULL
CLARK NEW YORK NULL
SCOTT DALLAS 2005-03-14 00:00:00.000
KING NEW YORK NULL
TURNER CHICAGO NULL
ADAMS DALLAS NULL
JAMES CHICAGO 2005-03-14 00:00:00.000
FORD DALLAS NULL
MILLER NEW YORK NULL
(14 行受影响)
解决方案1:
使用外连接既能够获得额外信息，又不会丢失原有的信息。
首先连接 EMP 表和 DEPT 表，得到全部员工和他们所在部门的位置。
然后外连接 EMP_BONUS 表，如果某个员工有奖金，则检索其收到奖金的日期。
select e.ename, d.loc, eb.received
from emp e join dept d
on (e.deptno=d.deptno)
left join emp_bonus eb
on (e.empno=eb.empno)
解决方案2：
使用标量子查询（即把子查询放置在 SELECT 列表里）来模仿外连接操作
select e.ename, d.loc,
(
select eb.received from emp_bonus eb
where eb.empno=e.empno
) as received
from emp e, dept d
where e.deptno=d.deptno
结果同上。
【分析】
-
外连接查询会返回一个表中的所有行，以及另一个表中与之匹配的行。外连接之所以能够解决本问题，是因为它不会过滤掉任何应该被返回的行。
-
在不破坏当前结果集的情况下，标量子查询是为现有查询语句添加额外数据的好办法。当使用标量子查询时，必须确保它们返回的是标量值（单值）。如果 SELECT 列表里的子查询返回多行，那么查询将会出错
3.7 确定两个表是否有相同的数据
问题：
你想知道两个表或两个视图里是否有相同的数据（行数和值）
为了距离我们新建一个视图V：
create view
as
select * from EMP
where deptno!=10
union all
select * from EMP
where ename='WARD'
视图的具体数据如下：
select * from V
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------- ---------- --------- ----------- ----------------------- ----------- ----------- -----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00.000 800 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00.000 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00.000 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00.000 2975 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00.000 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00.000 2850 NULL 30
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00.000 3000 NULL 20
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00.000 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00.000 1100 NULL 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00.000 950 NULL 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00.000 3000 NULL 20
7521 WARD SALESMAN 7698 1981-02-22 00:00:00.000 1250 500 30
(12 行受影响)
这个视图V的内容和MEP表的不同就是视图V不包含部门编号为10的员工，同时这个视图有同时包含两个ename=WARD的数据。
我们的问题就是要找到视图V和EMP表的不同之处：
我们渴望的结果是：
empno ename job mgr hiredate sal comm deptno cnt
----------- ---------- --------- ----------- ----------------------- ----------- ----------- ----------- -----------
7521 WARD SALESMAN 7698 1981-02-22 00:00:00.000 1250 500 30 1
7782 CLARK MANAGER 7839 1981-06-09 00:00:00.000 2450 NULL 10 1
7839 KING PRESIDENT NULL 1981-11-17 00:00:00.000 5000 NULL 10 1
7934 MILLER CLERK 7782 1982-01-23 00:00:00.000 1300 NULL 10 1
7521 WARD SALESMAN 7698 1981-02-22 00:00:00.000 1250 500 30 2
(5 行受影响)
解决方案：
使用关联子查询和 UNION ALL 找出那些存在于视图 V 而不存在于 EMP 表的数据，以及存在于 EMP 表而不存在于视图 V 的数据，并将它们合并起来。
select *
from
(
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno, count(*) as cnt
from emp e
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
)e
where not exists
(
select null
from
(
select v.empno,v.ename,v.job,v.mgr,v.hiredate,
v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
)v
where v.empno = e.empno
and v.ename = e.ename
and v.job = e.job
and v.mgr = e.mgr
and v.hiredate = e.hiredate
and v.sal = e.sal
and v.deptno = e.deptno
and v.cnt = e.cnt
and coalesce(v.comm,0) = coalesce(e.comm,0)
)
union all
select *
from
(
select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
)v
where not exists
(
select null
from
(
select e.empno,e.ename,e.job,e.mgr,e.hiredate,
e.sal,e.comm,e.deptno, count(*) as cnt
from emp e
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
)e
where v.empno = e.empno
and v.ename = e.ename
and v.job = e.job
and v.mgr = e.mgr
and v.hiredate = e.hiredate
and v.sal = e.sal
and v.deptno = e.deptno
and v.cnt = e.cnt
and coalesce(v.comm,0) = coalesce(e.comm,0)
)
其中：首先是查询视图e(由EMP表创建)中存在，而视图V中不存在的数据
select *
from
(
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno, count(*) as cnt
from emp e
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
)e
where not exists
(
select null
from
(
select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
)v
where v.empno = e.empno
and v.ename = e.ename
and v.job = e.job
and v.mgr = e.mgr
and v.hiredate = e.hiredate
and v.sal = e.sal
and v.deptno = e.deptno
and v.cnt = e.cnt
and coalesce(v.comm,0) = coalesce(e.comm,0)
)
结果如下：
empno ename job mgr hiredate sal comm deptno cnt
----------- ---------- --------- ----------- ----------------------- ----------- ----------- ----------- -----------
7521 WARD SALESMAN 7698 1981-02-22 00:00:00.000 1250 500 30 1
7782 CLARK MANAGER 7839 1981-06-09 00:00:00.000 2450 NULL 10 1
7839 KING PRESIDENT NULL 1981-11-17 00:00:00.000 5000 NULL 10 1
7934 MILLER CLERK 7782 1982-01-23 00:00:00.000 1300 NULL 10 1
(4 行受影响)
【注意】注意，这里比较的不是 EMP 表和视图 V ，而是内嵌视图 E 和内嵌视图 V 。
同理，UNION ALL 后面的查询语句做了相反的操作，它找出了所有存在于内嵌视图 V 而不存在于内嵌视图 E 的行。
如下:
select *
from (
select v.empno,v.ename,v.job,v.mgr,v.hiredate,
v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
) v
where not exists (
select null
from (
select e.empno,e.ename,e.job,e.mgr,e.hiredate,
e.sal,e.comm,e.deptno, count(*) as cnt
from emp e
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
) e
where v.empno = e.empno
and v.ename = e.ename
and v.job = e.job
and v.mgr = e.mgr
and v.hiredate = e.hiredate
and v.sal = e.sal
and v.deptno = e.deptno
and v.cnt = e.cnt
and coalesce(v.comm,0) = coalesce(e.comm,0)
)
empno ename job mgr hiredate sal comm deptno cnt
----------- ---------- --------- ----------- ----------------------- ----------- ----------- ----------- -----------
7521 WARD SALESMAN 7698 1981-02-22 00:00:00.000 1250 500 30 2
(1 行受影响)
3.8 识别并消除笛卡儿积
问题:
你想找出部门编号为 10 的所有员工的名字及其部门所在的城市。
解决方案：
select e.ename, d.loc
from emp e, dept d
where e.deptno = 10
and d.deptno = e.deptno
分析：你也许会说这个问题好像很简单呀！但是很多时候我们会忘记d.deptno = e.deptno这个限制条件
记不记得我们之前说过，没有where语句限制的连接查询就是交叉连接，返回的结果就是笛卡尔积
【注意】
为了消除笛卡儿积，我们通常会用到 n-1 法则，其中 n 代表 FROM 子句里表的个数，n-1 则代表消除笛卡儿积所必需的连接查询的最少次数。
依据表里有什么样的键以及基于哪些列来实现表之间的连接操作，有时候必要的连接查询次数可能会超过 n-1 次，但是当我们编写查询语句的时候，n-1 法则仍然是一个很好的指导原则。
3.9 组合使用连接查询与聚合函数
问题:
你想执行一个聚合操作，但查询语句涉及多个表。你希望确保表之间的连接查询不会干扰聚合操作。
例如；你希望计算部门编号为 10 的员工的工资总额以及奖金总和。
在这个问题中， EMP_BONUS 表里有如下数据。
EMP_BONUS 表中的 TYPE 列决定了奖金的数额。若 TYPE 值等于 1，则奖金为工资的 10%；若 TYPE 值等于2，则奖金为工资的 20%；若 TYPE 值等于 3，则奖金为工资的 30%。
select * from emp_bonus
EMPNO RECEIVED TYPE
----- ----------- ----------
7934 17-MAR-2005 1
7934 15-FEB-2005 2
7839 15-FEB-2005 3
7782 15-FEB-2005 1
首先，考虑下面的查询语句，它返回了部门编号为 10 的所有员工的工资和奖金。
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10
结果：
empno ename sal deptno bonus
----------- ---------- ----------- ----------- ------------
7934 MILLER 1300 10 130.0
7934 MILLER 1300 10 260.0
7839 KING 5000 10 1500.0
7782 CLARK 2450 10 245.0
(4 行受影响)
现在我们想要计算部门编号为10 的所有员工的工资总额和奖金总额，我们能按照下面SQL语句计算吗？
select deptno,sum(sal) as total_sal,sum(bonus) as total_bonus
from
(
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10
)X
group by deptno
结果：
deptno total_sal total_bonus
----------- ----------- ----------------
10 10050 2135.0
(1 行受影响)
上面结果中total_bonus是对的，但是 total_sal并不对，因为7934号员工得了两次奖金，所以他出现了两次在子查询结果中，最后在计算total_sal时，计算了两次。
简而言之，就是因为有部分员工多次获得奖金，所以在 EMP 表和 EMP_BONUS 表连接之后再执行聚合函数 SUM ，就会得出错误的计算结果。
解决方案：
在连接查询里进行聚合运算时，必须十分小心才行。如果连接查询产生了重复行，通常有两种办法来使用聚合函数，而且可以避免得出错误的计算结果。
法1：调用聚合函数时直接使用关键字 DISTINCT ，这样每个值都会先去掉重复项再参与计算；
SQL语句如下：
select deptno,
sum( distinct sal) as total_sal,
sum( bonus) as total_bonus
from
(
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10
)X
group by deptno
法2：在进行连接查询之前先执行聚合运算（以内嵌视图的方式），这样可以避免错误的结果，因为聚合运算发生在连接查询之前。
SQL语句如下：
select d.deptno,
d.total_sal,
sum(e.sal*case when eb.type = 1 then .1
when eb.type = 2 then .2
else .3 end) as total_bonus
from emp e,
emp_bonus eb,
(
select deptno, sum(sal) as total_sal
from emp
where deptno = 10
group by deptno
) d
where e.deptno = d.deptno
and e.empno = eb.empno
group by d.deptno,d.total_sal
其中子查询所得到的临时表d：
deptno total_sal
----------- -----------
10 8750
3.10 组合使用外连接查询与聚合函数
问题:
本节的问题和 3.9 节的大致相同:计算出了部门编号为 10 的员工的工资总额和奖金总额。
但是略微修改了 EMP_BONUS 表的数据，使得部门编号为10 的员工中只有部分人获得了奖金。
(上一题的EMP_BONUS 表有四行数据，其中一人得了两次奖金，但是10号部门（总共就3人）的所有人都是有奖金的)
EMP_BONUS表的数据：
EMPNO RECEIVED TYPE
------- -------- ---------
7934 17-MAR-2005 1
7934 15-FEB-2005 2
我们依旧按照上一节的SQL语句查询
select deptno,
sum( distinct sal) as total_sal,
sum( bonus) as total_bonus
from
(
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10
)X
group by deptno
结果是错误的，为什么呢？
我们来看其中的子查询：
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10
结果：
empno ename sal deptno bonus
----------- ---------- ----------- ----------- ------------
7934 MILLER 1300 10 130.0
7934 MILLER 1300 10 260.0
(2 行受影响)
所以按照上一题的SQL语句来查询，则查询没有计算部门编号为 10 的全部员工的工资总额，实际上只有 MILLER 的工资被计入总和，而且被错误地计算了两次。
解决方案1：
下面的解决方案也和 3.9 节的类似，不同之处在于要外连接 EMP_BONUS 表，确保把部门编号为 10 的全部员工都包括进来。
select deptno,
sum(distinct sal) as total_sal,
sum(bonus) as total_bonus
from
(
select e.empno,
e.ename,
e.sal,
e.deptno,
eb.type,
e.sal*case when eb.type is null then 0
when eb.type = 1 then .1
when eb.type = 2 then .2
else .3
end as bonus
from emp e left outer join emp_bonus eb
on (e.empno = eb.empno)
where e.deptno = 10
)X
group by deptno
结果：
deptno total_sal total_bonus
----------- ----------- ------------
10 8750 390.0
(1 行受影响)
注意其中的子查询
select e.empno,
e.ename,
e.sal,
e.deptno,
eb.type,
e.sal*case when eb.type is null then 0
when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e left outer join emp_bonus eb
on e.empno=eb.empno
where e.deptno=10
注意看：为什么case语句比上一题中多了一个when eb.type is null then 0,因为有一部分的10号部门的员工是没有奖金的，所以外连接的时候，eb.type是null
这个子查询的结果：
empno ename sal deptno type bonus
----------- ---------- ----------- ----------- ----------- -----------
7782 CLARK 2450 10 NULL 735.0
7839 KING 5000 10 NULL 1500.0
7934 MILLER 1300 10 1 130.0
7934 MILLER 1300 10 2 260.0
(4 行受影响)
解决方案2:
首先计算部门编号为 10 的员工的工资总额，然后再连接 EMP 表和 EMP_BONUS 表（这样就避免了使用外连接）。
select d.deptno,
d.total_sal,
sum(e.sal*case when eb.type = 1 then .1
when eb.type = 2 then .2
else .3
end) as total_bonus
from emp e,
emp_bonus eb,
(
select deptno, sum(sal) as total_sal
from emp
where deptno = 10
group by deptno
) d
where e.deptno = d.deptno
and e.empno = eb.empno
group by d.deptno,d.total_sal
3.11 从多个表中返回缺失值
问题:
你想从多个表中返回缺失值。(换言之，就是使用某种连接，将两个表相连接，之后找到两个表的所有相匹配的数据，包括空值)
列出所有的部门，以及该部门的所有员工（如果这个部门有员工的话）
select d.deptno,d.dname,e.ename
from dept d left outer join emp e
on (d.deptno=e.deptno)
列出所有的员工，以及该员工所属的部门（如果这个员工有部门的话，当然我们的表中所有的员工都是有部门的，这里仅仅是为了举例子）
select d.deptno,d.dname,e.ename
from dept d right outer join emp e
on (d.deptno=e.deptno)
那么我们想要把所有的员工和部门都列出来，不论该部门是否有员工，也不论某个员工是否有部门
select d.deptno,d.dname,e.ename
from dept d left outer join emp e
on (d.deptno=e.deptno)
union
select d.deptno,d.dname,e.ename
from dept d right outer join emp e
on (d.deptno=e.deptno)
其实我们可以使用全连接full outer join
select d.deptno,d.dname,e.ename
from dept d full outer join emp e
on (d.deptno=e.deptno)
【说明】
全外连接查询其实就是合并两个表的外连接查询的结果集
3.12 在运算和比较中使用 Null
问题：
Null 不会等于或不等于任何值，甚至不能与其自身进行比较，但是你希望对从 Null 列返回的数据进行评估，就像评估具体的值一样。
例如：
你想找出 EMP 表里业务提成（ COMM列）比员工 WARD 低的所有员工。检索结果应该包含业务提成为 Null 的员工。、
解决方案：使用使用 coalesce() 函数将 Null 值替代为实际值。（coalesce函数说明见 1.12）
coalesce 函数会返回参数列表里的第一个非 Null 值
select ename , comm
from emp
where coalesce(comm, 0)<(select comm from emp where ename='WARD')
