SQL Cookbook（读书笔记）No.3

三、操作多个表

3.1 记录集的叠加

Q:要将多个表的数据组织一起，就像一个结果叠加在另一个上面，这些表不必有相同的关键字，但是，他们对应列的数据类型应相同。

Ex,要显示EMP表部门10中员工的名字和部门编号，以及DEPT表中每个员工的名字和部门编号，结果集如下图：

  

A:使用集合操作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

PS:所有SELECT列表中的项目数和对应的数据类型必须匹配，错误的查询语句：

--查询一
select deptno
    from emp
 union all
select deptno,dname
    from dept
--查询二
select deptno
    from emp
 union all
select ename
    from dept

注：UNION ALL 包含重复的项目。可以使用 UNION 筛选掉重复的项目。

select deptno
    from emp
 union 
select deptno
    from dept

EMP.DEPTNO 和 DEPT.DEPTNO 的 UNION 只返回4 行：

如果使用UNION而不是UNION ALL，很可能会为了去除重复项而进行排操作。处理大结果集时要记住,使用 UNION 子句大致等价下面的查询，对 UNION ALL 子句的查询结果使用DISTINCT:

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

查询不要使用DISTINCT,除非有必要；对于UNION而言也是如此。一般使用UNION ALL,而不使用 UNION。

 3.3  在两个表中查找共同行

Q:查找两张表的共同行，但又多列可以用来联接的这两个表。Ex,下面的视图:

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

select * from V

现要返回表EMP中与视图得到的行相匹配的所以职员的ENPNO、ENAME、JOB、SAL和DEPTNO,得到如下结果集：

A:  列进行联接或使用集合操作INTERSECT,返回两个表的交集。

--使用多个联接条件，将表和视图联接
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谓词
select ename,ename,job,sal,deptno
  from emp
 where (ename,job,sal) in (
    select ename,job,sal from emp
    intersect
    select ename,job,sal from V
)

PS:集合操作INTERSECT将返回两个行来源中的共同行。在使用INTERSECT时，要求两个标的项数数目相同，对应的数据类型也相同。默认情况下不会返回重复行。

3.4 从一个表中查找另一个表没有的值

Q:要从一个表中查找另一个表中不存在的值。Ex:从表DEPT中查找在表EMP中不存在的数据的所有部门。示例数据中DEPTNO的值为40的记录在EMP中不存在，所以结果集应该如下

deptno
-----------
40

S:SQL Sever不支持差集函数

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

 

(To Be Continue)