SQLCookBook第三章学习日记5

第三章 操作多个表

---

本章介绍如何使用联接和集合操作，将多个表的数据组合在一起，联接 是SQL的基础。集合操作也非常重要。本书后续章节的复杂查询。都已本章的联接和集合操作为基础。

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

讨论：union all 将多个来源的行组合起来，放到一个结果集中。所有select 列表中的项目数和对应项目的数据类型必须要匹配。这跟其他所有集合的操作要求相同。例如，下面的两个查询将会失败：

select deptno from dept 
    union all 
select ename emp

select deptno,dname from dept
    union 
select deptno from emp

务请注意union all 将包括重复的项目。如果要筛选掉重复项，可以使用union运算符。例如，emp.deptno和dept.deptno之间的union将只返回四行。

select deptno from emp
    union 
select deptno from dept

如果使用union而不是union all ，很可能会为了去除重复项而进行排序操作。在处理强大结果集时要记住，使用union 子句大致等价于下面的查询，对union all 子句的查询结果使用distinct：

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

通常，查询中不要使用distinct，除非确有必要这样做，对于union而言也是如此，除非确有必要，一般使用union all 而不使用union.

3.2 组合相关的行

问题：多个表有一些相同列，或有些列的值相同，要通过联接这些列得到结果。例如，要显示部门10中的所有员工的名字，以及每个员工所在部门的工作地点，这些数据存储在两个独立的表中。

解决方案：根据drptno联接表emp和dept：

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

讨论：

该解决方案是联接的一种，更准确的说是等值联接，这是内连接的一种类型。联接操作会将来自两个表的行组合到一个表中。

等值联接的联接条件是相等条件，比如说买两个表的部门编号相等。

内连接是联接的原始类型，返回的每一行都包含来自每个表的数据。

从概念上来讲，要得到联接的结果集，首先要创建from子句后列出的表的笛卡尔积（行的所有可能组合）

select e.ename,d.loc, e.deptno as emp_deptno, d.deptno as dept_deptno
    from emp e , dept d
where e.deptno = 10

这将返回表emp部门10中的所有员工，以及表dept的所有部门。然后再where子句的表达式中使用e.deptno和d.deptno来限制结果集，只返回emp.deptno和dept.deptno相等的哪些行。

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

还有一种解决方案就是利用显式的join子句（inner关键字可选）

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

如果希望将联接逻辑放在from子句中，而不是在where子句中，可以使用join子句，这两种方式都符合ansi标准，而且在本书设计的所有rdbms的最新版本中都适用。

3.3在两个表中查找共同行

问题：查找两个表中共同行，但有多列可以用来连接的这两个表。例如，考虑下面的视图V：

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

select * from V

视图V只返回了职员的数据，然而，这个视图并没有显示出emp的所有列。现需要返回表emp中与视图得到的行相匹配的所有职员的empno、ename、job、sal和deptno

解决方案：要返回正确的结果，必须按照所有必要的列进行连接。或者，如果不想进行连接，也可以使用集合操作intersect，返回两个表的交集（共同的行）。

MySQL和SQL Server

使用多个连接操作，将表emp与视图V联接起来

select e.empno,e.name,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
        )

DB2、Oracle 和 PostgreSQL
MySQL和SQL Server解决方案也可以用于DB2 Orancle 和PostgreSQL。如果需要从视图V中返回值，就要使用这个解决方案。

如果实际上并不需要从视图V中返回列，可以使用集合操作intersect以及in谓词：

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
    )

讨论：
在执行联接时，为了返回正确的结果，必须考虑按适当的列联接。当某些列中含有相同的值，而其他列中包含有不同的值时，这一点尤其重要。

集合操作intersect将返回两个行来源中的共同行。在使用intersect时，要求两个表的项目数目相同，对应的书库类型也相同。使用集合操作的时候要注意，默认情况下，不会返回重复行。

---