SQL笔记| 外连接的用法

本节，通过学习格式转换中具有代表性的行列转换和嵌套式侧栏的生成方法，深入理解一下其中起着重要作用的外连接。

用外连接进行行列转换（1）（行->列）：制作交叉表

-- 水平展开求交叉表（1）：使用外连接
SELECT C0.name
 CASE WHEN C1.name IS NOT NULL THEN 'O' ELSE NULL END AS 'SQL入门',
 CASE WHEN C2.name IS NOT NULL THEN 'O' ELSE NULL END AS 'UNIX基础',
 CASE WHEN C3.name IS NOT NULL THEN 'O' ELSE NULL END AS 'JAVA中级'
 FROM (SELECT DISTINCT name FORM Courses) C0	-- 这里的C0是侧栏
 LEFT OUTER JOIN (SELECT name FORM Courses WHERE course= 'SQL入门' ) C1 ON C0.name=C1.name 
 LEFT OUTER JOIN (SELECT name FORM Courses WHERE course= 'UNIX基础' ) C2 ON C0.name=C2.name 
 LEFT OUTER JOIN (SELECT name FORM Courses WHERE course= 'UNIX基础' ) C3 ON C0.name=C3.name  ;
-- 水平展开（2）：使用标量子查询
SELECT C0.name,
 (SELECT 'O' FROM  Courses C1 WHERE course= 'SQL入门'  AND C0.name=C1.name ) AS  "SQL入门",
 (SELECT 'O' FROM  Courses C2 WHERE course= 'UNIX基础'  AND C0.name=C2.name ) AS "UNIX基础" ,
 (SELECT 'O' FROM  Courses C3 WHERE course= 'JAVA中级'  AND C0.name=C3.name ) AS  "JAVA中级"
 FROM  (SELECT DISTINCT name FORM Courses) C0;

这种做法不仅有利于应对需求变更，对于需要动态生成SQL的系统也是有好处的，缺点是性能不太好。目前，在SELECT子句中使用标量子查询（或者关联子查询），性能开销还是相当大的，因为标量子查是针对SELECT返回的每一行来执行的。
接下来介绍第3种方法，即嵌套使用CASE表达式。CASE表达式可以写在SELECT子句里的聚合函数内部，也可以写在聚合函数外部。

-- 水平展开（3）：嵌套使用CASE表达式
SELECT name,
 CASE WHEN SUM(CASE WHEN course= 'SQL入门'   THEN 1  ELSE NULL END ) = 1 THEN 'O' ELSE NULL END AS "SQL入门" ,
 CASE WHEN SUM(CASE WHEN course= 'UNIX基础'   THEN 1  ELSE NULL END ) = 1 THEN 'O' ELSE NULL END AS "UNIX基础" ,
 CASE WHEN SUM(CASE WHEN course= 'JAVA中级'   THEN 1  ELSE NULL END ) = 1 THEN 'O' ELSE NULL END AS "JAVA中级"
 FROM Courses 
 GROUP BY name;

用外连接进行行列转换（2）（行->列）：汇总重复项于一列

-- 列数据转换成行数据：使用UNION ALL
CREATE VIEW Children(child) AS
SELECT employee,child_1 AS child FROM Personnel 
UNION ALL
SELECT employee,child_2 AS child FROM Personnel 
UNION ALL
SELECT employee,child_3 AS child FROM Personnel ;

UNION ALL 不会排除重复行。

-- 获取员工子女列表的SQL语句（没有孩子的员工也要输出）
SELECT EMP.employee,CHILDREN.child 
 FROM Personnel EMP
 LEFT OUTER JOIN Children ON CHILDREN.child IN (EMP.child_1,EMP.child_2,EMP.child_3);

在交叉表里制作嵌套表侧栏

思路是以两张表作为主表进行外连接操作

-- 使用外连接生成嵌套表侧栏
SELECT  MASTER.age_calss AS  age_Class , MASTER.sex_cd AS sex_cd, DATA.pop_donbei AS pop_dongbei,DATA.pop_guangdong AS pop_guandong 
 FROM (SELECT age_calss,sex_cd FROM TblAge CROSS JOIN TblSex ) MASTER
  LEFT OUTER JOIN (SELECT age_class,sex_cd, SUM(CASE WHEN pref_name IN ('','') THEN population ELSE NULL END) AS pop_dongbei,SUM(CASE WHEN pref_name IN ('','') THEN population ELSE NULL END) AS pop_guandong FROM TblPop GROUP BY age_class,sex_cd)DATA  ON MASTER.age_calss = DATA.age_class  AND MASTER.sex_cd = DATA.sex_cd ;

作为乘法运算的连接

在SQL里，交叉连接相当于乘法运算。

--解法（1）：通过在连接前聚合来创建一对一的关系
SELECT I.itme_no,SH.total_qty
 FROM Items I LEFT OUTER JOIN (SELECT item_no,SUM(quantity) AS total_qty FROM SalesHistory GROUP BY item_no) SH  ON I.item_no = SH.item_no ;
-- 解法（2）：先进行一对多的连接再聚合
SELECT I.itme_no,SUM(SH.quantity)
 FROM Items I LEFT OUTER JOIN  SalesHistory  SH  ON I.item_no = SH.item_no   -- 一对多连接
 GROUP BY I.item_no;

这种做法代码更简洁，而且没有使用临时视图，所以性能也会有所改善。一对一或一对多关系的两个集合，在进行连接操作后行数不会（异常地）增加。

全外连接

标准SQL里定义了3种类型的外连接：
左外连接（LEFT OUTER JOIN)
右外连接（RIGHT OUTER JOIN)
全外连接（FULL OUTER JOIN)

-- 全外连接保留全部信息
SELECT COALESCE(A.id,B.id) AS id, A.name AS A_name,B.name AS B_name 
 FROM Class_A A FULL OUTER JOIN Class_B B ON A.id=B.id ;
--数据库不支持全外连接时的替代方案
SELECT A.id AS id, A.name ,B.name  
 FROM Class_A A FULL LEFT JOIN Class_B B ON A.id=B.id
UNION 
SELECT A.id AS id, A.name ,B.name  
 FROM Class_A A FULL RIGHT JOIN Class_B B ON A.id=B.id ;

其实，我们可以换个角度，把表连接看成集合运算。内连接相当于求集合的积（INTERSECT,也称交集），全外连接相当于求集合的和（UNION,也称并集）。

用外连接进行集合运算

UNION是SQL-86标准开始加入的，INTERSECT和EXCEPT都是SQL-92标准才加入的。关系乘法还没有被标准化。各个DBMS供应商在功能的实现程度上也有所不同，参差不齐。集合运算符会进行排序，所以可能会带来性能上的问题。
尽管在SQL中NULL通常被认为是不好的，但我们还是要通过先使用外连接生成NULL，再将其排除掉来计算差集。

-- 用外连接求差集：A - B
SELECT A.id AS id, A.name ,B.name  
 FROM Class_A A FULL LEFT JOIN Class_B B ON A.id=B.id
 WHERE B.name IS NULL ;
-- 用外连接求差集：B - A 
SELECT A.id AS id, A.name ,B.name  
 FROM Class_A A FULL RIGHT JOIN Class_B B ON A.id=B.id
 WHERE A.name IS NULL ;
-- 用全连接求异或集
SELECT COALESCE(A.id,B.id) AS id,COALESCE( A.name ,B.name） AS name 
 FROM Class_A A FULL OUTER JOIN Class_B B ON A.id=B.id 
 WHERE A.name IS NULL OR B.name IS NULL;
-- 用外连接进行关系除法运算：差集的应用
SELECT DISTINCT shop 
 FROM ShopItems SI1
 WHERE NOT EXISTS (SELECT I.item FROM Items I LEFT OUTER JOIN ShopItems SI2 ON I.item = SI2.item AND SI1.shop = SI2.shop WHERE SI2.item IS NULL) ;

另外，OUTER也是可以省略的，所以我们也可以写成LEFT JOIN和FULL JOIN（标准SQL也是允许的）。但为了区分是内连接还是外连接，最好还是协商OUTER。
本节要点：
1.SQL不是用来生成报表的语言，所以不建议用它来进行格式转换。
2.必要时考虑用外连接或CASE表达式来解决问题。
3.生成嵌套式侧栏时，如果先生成主表的笛卡儿积再进行连接，很容易就可以完成。
4.从行数来看，表连接可以看成乘法。因此，当表之间是一对多的关系时，连接后行数不会增加。
5.外连接的思想和集合运算很像，使用外连接可以实现各种集合运算。