SQL经典实例（三）多表查询

摘要：组合使用连接查询与聚合函数例如，希望计算部门编号为的员工工资总额以及奖金总和。因为有部分员工多次获得奖金，所以在表和表连接之后再执行聚合函数，会得出错误的计算结果。

显示EMP表里部门编号为10的员工姓名和部门编号，以及DEPT表中各个部门的名称和编号。

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;

考虑如下视图V：

从EMP表中获取与视图V相匹配的全部员工的EMPNO, ENAME, JOB, SAL, DEPTNO。

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 );

使用集合运算INTERSECT和谓词IN

集合运算INTERSECT会返回两个行集的相同部分，在使用INTERSECT时，必须保证两个表里参与的项目数目是相同的，并且数据类型也是相同的。注意，当执行集合运算时，默认不会返回重复项。

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

Oracle

select deptno from dept
minus
select deptno from emp;

MySQL(应注意IN与NOT IN的区别，看下节)

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

IN与NOT IN本质上是OR运算，由于NULL值参与OR逻辑运算的方式不同，IN和NOT IN将会产生不同的结果。如：

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);

因为

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

等价于

deptno <> 10 and deptno <> 50 and deptno <> null

而对于null来说，它应该理解为未知(UNKNOWN)，在任何等式中，等价于UNKNOWN的判断均不会返回任何值。
参考Oracle官方对NULL值的解释

为了避NOT IN和NULL值带来的问题，可以结合NOT EXISTS和关联子查询。

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

例如：找出哪些部门没有员工
MySQL及Oracle 9i和后续版本

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

Oracle 8i及更早版本

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

保留不匹配的行，这种操作可以成为反连接，为了更好理解，可以看一下过滤null值之前的结果集：

select e.ename, e.deptno as emp_deptno, d.*
    from dept d left outer join emp e
        on (d.deptno = e.deptno);

所以解决方案就是使用where子句，只保留EMP_DEPTNO是NULL值的结果集。

考虑如下视图：

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

要找出该视图与EMP表的不同数据和重复数据：

也就是说，我们要找出存在于EMP表而不在视图V中的DEPTNO为10的三条数据，以及在EMP表中ENAME为WARD的一条数据和在视图V中ENAME为WARD的两条重复数据。

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

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

之所以使用group by是为了找到在每个表或者视图中找到重复数据并统计CNT，以作为结果集的字段出现。

例如，希望计算部门编号为10的员工工资总额以及奖金总和。因为有部分员工多次获得奖金，所以在EMP表和EMP_BONUS表连接之后再执行聚合函数SUM，会得出错误的计算结果。EMP_BONUS表数据如下：

显然EMP_BONUS表中EMPNO数据有重复。先查看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;

所以按照如下方式进行工资和奖金的总和计算，将导致工资总额发生错误：

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;

可以使用distinct关键字：
MySQL & 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 * 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;

Oracle也可以使用窗口函数SUM OVER
Oracle

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;

关于窗口函数，可以查看本书附录

《SQL经典实例》第三章