SQL技巧（3）多表操作

（Molinaro-SQL cookbook笔记）

1. 记录叠加

叠加的表不必有相同的关键字，但是对对应列的数据类型必须相同。

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
---------------------
ACCOUNTING	10
RESEARCH	20
SALES	30
OPERATIONS	40

UNION ALL将包括重复的项目，如果需要去重可使用UNION

使用UNION时和可能会为去重而进行排序，大致等价于对UNION ALL的查询结果使用DISTINCT

select deptno
from emp
union
select deptno
from dept

大致等价于

select distinct deptno
from (
      select deptno
      from emp
      union all
      select deptno
      from dept
     )

通常查询中不要使用DISTINCT，除非有确切的理由必须这样做

2. 组合相关的行

显示部门10中所有员工的名字和所在部门的工作地点，这些数据存储在两个独立的表中

select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.deptno = 10

该方法是equi-join， 是inner join的一种类型

也可以显示的使用JOIN子句

select e.ename, d.loc
from emp e inner join dept d on (e.deptno = d.deptno)
where e.deptno = 10

如果希望把join逻辑放在FROM中， 而不是WHERE中，可使用JOIN字句，两种方法都符合ANSI标准

3. 在两个表中查找共同行

有多列可以用来连接两个表，如以下视图

create view V
as 
select ename, job, sal
from emp
where job = 'CLERK'
select * from V

现在需要返回表EMP中与视图得到的行相匹配的所有职员的EMPNO, ENAME, JOB, SAL, DEPTNO

MySQL和SQL Server

使用多个连接条件

select e.emptno, 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.emptno, 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）

DB2， Oracle和PostgreSQL

如果实际上并不需要从V中返回列，可以使用集合操作INTERSACT和IN谓词

select empno, ename, job, sal, deptno
from emp
where (ename, job, sal) in (
          select ename, job, sal from emp
          intersact
          select ename, job, sal from V
         )

INTERSACT要求两个表列数相同且对应数据类型也相同

4. 从一个表中查找另一个表不存在的值

从表DEPT中查找表EMP中不存在数据的所有部门

DB2和PostgreSQL

select deptno from dept
except
select deptno from emp

Oracle

select deptno from dept
minus
select deptno from emp

EXCEPT和MINUS要求两个表列数和数据类型必须匹配，不会返回重复行

MySQL和SQL Server

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

可以使用DISTINCT确保每个在EMP中没有的DEPTNO值只在结果中出现一次

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

使用NOT IN时， 一定要注意NULL值

IN和NOT IN本质上是OR运算，计算逻辑OR时处理NULL的方式不同，产生的结果也不同

所以

select deptno
from dept
where deptno in (10, 50, null)

等价于

select deptno
from dept
where (deptno =10 or deptno = 50 or deptno = null)

对于NOT IN

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)

在这种情况下， 当DEPTNO为50时，表达式输出为

not (deptno = 10 or deptno = 50 or deptno = null)
= not (false or true or null)
= not (true or null)
= false

在SQL中，true or null = true， false or null = null

可以使用NOT EXIST解决NOT IN和NULL的问题：

select d.deptno
from dept d
where not exist (
                 select null
                 from emp e
                 where d.deptnp = e.deptno
                )

EXIST/NOT EXIST子查询中SELECT列表中的项目并不重要，因此这里选择了NULL

5. 从一个表中查找与其他表不匹配的记录

查找没有职员的部门，与前一个问题的不同之处是此查询要求可以直接列出DEPT中其他的列

DB2， MySQL，PostgreSQL，SQL Server

使用outer join和NULL筛选(outer可省略)

select d.*
from dept d left outer join emp e on (d.deptno = e.deptno)
where e.deptno is null

Oracle

可以使用前一解决方案，也可以使用Oracle特有的outer join语法

select d.*
from dept d, emp e 
where d.deptno = e.deptno (+) and e.deptno is null

6.向查询中增加连接而不影响其他连接

已经有了一个查询可以返回所需要的值，但还需要添加其他信息，但加入信息（inner join）时会导致返回数据丢失
DB2，MySQL， PostgreSQL，SQL Server

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)

Oracle

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 (+)

所有平台上都可运行的标量子查询（scalar subquery， 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

使用标量子查询时必须确保返回的是单个值，否则将会出现错误

7. 返回两个表中不同的数据

检测这个视图与EMP中的数据是否完全相同

create view V
as
select * from emp where deptno != 10
union all
select * from emp where ename = 'WARD'
select * from V

其中WARD行重复，解决方案需显示重复行
DB2和PostgreSQL

(
select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
from V
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
except
select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
from emp
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
)
union all
(
select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
from emp
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
except
select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
from V
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
)

Oracle

(
select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
from V
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
minus
select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
from emp
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
)
union all
(
select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
from emp
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
minus
select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
from V
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
)

MySQL和SQL Server

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 exist (
                 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 exist (
                 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)
)

所有方法的原理都是一样的：

（1）找出EMP中存在而V中没有的行

（2）UNION ALL在V中存在而EMP中没有的行

8. 识别和消除笛卡尔积

n-1规则：FROM子句中表的数量为n，则n-1为避免笛卡尔积的最小join数目。根据表中的关键字和连接列不同，可能需要超过n-1个join。

9. 聚集（aggregates）和联接（joins）

表EMP_BONUS包含如下数据

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

然而，为了计算总奖金而跟EMP_BONUS做join时， 错误出现了

select deptno, sum(sal) as total_sal, sum(bonus) as total_bonus
from (
       select e.empno, e.ename, e.sal, e.deptno, e.sal, 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

尽管total_bonus是正确的，但是total_sal却是错误的，因为join导致sal产生了重复

当处理aggregate和join混合的查询是，一定要小心join产生的重复行导致aggre函数计算错误。

MySQL和PostgreSQL

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, 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

此解决方案只是把不同的EMP.SAL值相加，下面有另一种解决方法，先计算部门10总工资，再与EMP联接，最后联接EMP_BONUS，适用于所有DBMS：

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

DB2，Oracle，SQL Server

这些平台也支持上面的解决方案，还支持一种使用窗口函数SUM OVER的方案

select distinct deptno, total_sal, total_bonus
from (
      select e.empno, e.ename, sum(distinct e.sal) over (partition by e.deptno) as total_sal, e.deptno,  
                                                                  sum (e.sal*case when eb.type = 1 then .1
                                                                              when eb.type = 2 then .2
                                                                              else .3
                                                                              end) over
                                                                 (partition by deptno) as total_bonus
      from emp e, emp_bonus eb
      where e.empno = eb.empno and e.deptno = 10
     )x

 

10. 聚集和外联接

如果EMP_BONUS中不是每个员工都有奖金，则需要用外联接来确保部门10中所有员工都包含在结果中：

DB2，MySQL，PostgreSQL和SQL Server

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, 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

Oracle

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, 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, emp_bonus eb
       where e.empno = eb.empno (+) and e.deptno = 10
     )x
group by deptno

上面两种解决方案都对CASE语句做了修改，下面是另一种解决方案，首先计算部门10的工资总额，然后联接到EMP，最后再联接到EMP_BONUS（避免了外联接），适用于所有DBMS：

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

11. 从多个表中返回丢失的数据

LEFT OUTER JOIN和RIGHT OUTER JOIN只能返回一个表中丢失的数据

DB2，MySQL，PostgreSQL和SQL Server

显式使用FULL OUTER JOIN返回两个表丢失的行以及所有匹配行

select d.deptno, d.dname, e.ename
from dept d full 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)
union
select d.deptno, d.dname, e.ename
from dept d left outer join emp e on (d.deptno = e.deptno)

Oracle

还可以使用Oracle独有的外联接语法

<pre name="code" class="sql">select d.deptno, d.dname, e.ename
from dept d, emp e
where d.deptno = e.deptno (+)
union
select d.deptno, d.dname, e.ename
from dept d, emp e 
where d.deptno(+) = e.deptno

12. 在运算和比较时使用NULL值

NULL永远不会等于或不等于任何值（包括自身） ，但有时需要像计算真实值一样计算可为空的列的返回值

例如，需要在EMP中查找所有比"WARD"提成低的员工，提成为NULL的员工也应当包括在内

select ename, comm
from emp
where coalesce(comm, 0) < ( select comm
                            from emp
                            where ename = 'WARD' )