第3章 表连接和集合运算

3.1 合并多个行集

想返回存储在多张表中的数据，即将多个结果集合并。这些表并非必须有相同的键，但它们的列的数据类型必须相同。

使用集合运算 UNION ALL 合并来自多张表的行。

UNION ALL 不会剔除重复的行。要剔除重复的行，可以使用运算符 UNION。使用 UNION（而不是 UNION ALL）时，很可能引发排序操作以消除重复的行。处理大型结果集时，务必牢记这一点。

除非必要，否则不要在查询中使用 DISTINCT。这条规则也适用于 UNION：除非必要，否则不要使用UNION，而应该使用 UNION ALL。

3.2 合并相关的行

想执行基于相同列或相同列值的连接，以返回多张表中的行。

基于 DEPTNO 连接 EMP 表和 DEPT 表。

select e.ename, d.loc
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

3.3 查找两张表中相同的行

想找出两张表中相同的行，但需要连接多列。

基于必要的列将表连接起来，以返回正确的结果。也可以使用集合运算 INTERSECT 来返回两张表的交集（两张表中相同的行），这样可以避免执行连接操作。

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 从一张表中检索没有出现在另一张表中的值

想找出一张表（源表）中没有出现在目标表中的值

解决这个问题时，计算差集的函数很有用。DB2、PostgreSQL、SQL Server 和 Oracle 都支持差集运算。如果你使用的 DBMS 没有提供计算差集的函数，则可以像 MySQL 解决方案那样使用子查询。

select deptno from dept
except 
select deptno from emp

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

3.5 从一张表中检索在另一张表中没有对应行的行

有两张包含相同键的表，你想从一张表中找出在另一张表中没有与之匹配的行。

返回一张表中的所有行，以及在另一张表中可能有匹配行也可能没有匹配行的行。然后，只留下没有匹配行的行。

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

3.6 在查询中添加连接并确保不影响其他连接

使用外连接来获得额外的信息，同时避免返回的数据比原来的查询少。先将 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)  
order by 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  
order by 2

3.7 判断两张表包含的数据是否相同

想知道两张表或两个视图中包含的数据（包括基数和值）是否相同。

DB2 和 PostgreSQL
使用集合运算 EXCEPT 计算视图 V 和 EMP 表的差集以及 EMP 表和视图 V 的差集，然后使用集合运算UNION ALL 合并这两个差集。

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

3.8 返回多张表中不匹配的行

返回多张表中不匹配的行。要返回 DEPT 表中不与 EMP 表中任何行匹配的行（没有任何员工的部门），需要使用外连接。

使用显式命令 FULL OUTER JOIN 返回两张表中匹配的行以及不匹配的行。

select d.deptno,d.dname,e.ename
from dept d 
full outer join emp e     
on (d.deptno=e.deptno)

MySQL 还不支持 FULL OUTER JOIN，因此需要使用 UNION 合并两个外连接的结果。

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)

3.9 在运算和比较中使用NULL

NULL 与包含自己在内的任何值都不相等，也不会相等，但你想像评估实际值一样评估可为 NULL 的列返回的值。例如，你想在 EMP 表中找出业务提成（COMM）比 WARD 低的所有员工，包括业务提成为 NULL的员工。

在标准评估中，可以使用诸如 COALESCE 等函数将 NULL 转换为实际值。

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