本文是《sql进阶教程》阅读笔记，感兴趣可以阅读该书对应章节，这本适合有一定sql基础的同学阅读。另外作者《sql基础教程》也值得一看
外链接的用法
本节，我们将通过学习格式转换中具有代表性的行列转换和嵌套式侧栏的生成方法，深入理解一下其中起着重要作用的外连接。
一、 用外连接进行行列转换 (1)（行→列）：制作交叉表
案例：这里有一张用于管理员工学习过的培训课程的表，如下所示
Courses
| name（员工姓名） | course（课程） |
|---|---|
| 赤井 | SQL 入门 |
| 赤井 | UNIX 基础 |
| 铃木 | SQL 入门 |
| 工藤 | SQL 入门 |
| 工藤 | Java 中级 |
| 吉田 | UNIX 基础 |
| 渡边 | SQL 入门 |
利用上面这张表生成下面这样一张交叉表（“课程学习记录一览表”）。○表示已学习过，NULL 表示尚未学习
课程学习记录一览表（表头：课程；侧栏：员工姓名）
| SQL 入门 | UNIX 基础 | Java 中级 | |
|---|---|---|---|
| 赤井 | ○ | ○ | |
| 工藤 | ○ | ○ | |
| 铃木 | ○ | ||
| 吉田 | ○ | ||
| 渡边 | ○ |
尝试用外连接的思路来思考，这样就可以知道，以侧栏（员工姓名）为主表进行外连接操作就可以生成表
-- 创建表
CREATE TABLE Courses(
id serial PRIMARY KEY,
name VARCHAR(20),
course varchar(20)
);
-- 插入数据
INSERT INTO Courses(name,course)VALUES
('赤井','SQL 入门'),
('赤井','UNIX 基础'),
('铃木','SQL 入门'),
('工藤','SQL 入门'),
('工藤','Java 中级'),
('吉田','UNIX 基础'),
('渡边','SQL 入门');
-- 下面方式并不能获取到想要的结果。
SELECT C0.name,
CASE WHEN C1.name IS NOT NULL AND course = 'SQL 入门' THEN '○' ELSE NULL END AS "SQL 入门",
CASE WHEN C1.name IS NOT NULL AND course = 'UNIX 基础' THEN '○' ELSE NULL END AS "UNIX 基础",
CASE WHEN C1.name IS NOT NULL AND course = 'Java 中级' THEN '○' ELSE NULL END AS "Java 中级"
FROM (SELECT DISTINCT name FROM Courses) C0
LEFT JOIN Courses C1 ON C1.name = C0.name;
注意：上面的方法不能获取正确的结果，会出现多行。 这种方式只适合还需要添加聚合函数的场景。
方法一：
--水平展开求交叉表(1)：使用外连接
SELECT C0.name,
CASE WHEN C1.name IS NOT NULL THEN '○' ELSE NULL END AS "SQL 入门",
CASE WHEN C2.name IS NOT NULL THEN '○' ELSE NULL END AS "UNIX 基础",
CASE WHEN C3.name IS NOT NULL THEN '○' ELSE NULL END AS "Java 中级"
FROM (SELECT DISTINCT name FROM Courses) C0
-- 这里的C0 是侧栏
LEFT OUTER JOIN(SELECT name FROM Courses WHERE course = 'SQL 入门' ) C1
ON C0.name = C1.name
LEFT OUTER JOIN(SELECT name FROM Courses WHERE course = 'UNIX 基础' ) C2
ON C0.name = C2.name
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'Java 中级' ) C3
ON C0.name = C3.name;
使用子查询，根据源表 Courses 生成 C0 ～ C3 这 4 个子集；SQL 中指定了名称的表和视图都相当于集合。因此，这里将生成下面这样 4 个集合：
C0 包含了全部员工，起到了“员工主表”的作用；C1 ～ C3 是每个课程的学习者的集合。这里以 C0 为主表，依次对 C1 ～ C3 进行外连接操作；通过 CASE 表达式将课程列中员工的姓名转换为○就算完成了
这种做法具有比较直观和易于理解的优点，但是因为大量用到了内嵌视图和连接操作，代码会显得很臃肿。而且，随着表头列数的增加，性能也会恶化。
方法二：
一般情况下，外连接都可以用标量子查询替代，因此可以像下面这样写：
-- 水平展开(2)：使用标量子查询
SELECT C0.name,
(SELECT '○'
FROM Courses C1
WHERE course = 'SQL 入门'
AND C1.name = C0.name) AS "SQL 入门",
(SELECT '○'
FROM Courses C2
WHERE course = 'UNIX 基础'
AND C2.name = C0.name) AS "UNIX 基础",
(SELECT '○'
FROM Courses C3
WHERE course = 'Java 中级'
AND C3.name = C0.name) AS "Java 中级"
FROM (SELECT DISTINCT name FROM Courses) C0;
这里的要点在于使用标量子查询来生成 3 列表头；
想加入第 4 列“PHP 入门”时，只需要在 SELECT 子句的最后加上相似语句就可以了。这种做法不仅利于应对需求变更，对于需要动态生成 SQL 的系统也是很有好处的。缺点是性能不太好，目前在 SELECT 子句中使用标量子查询（或者关联子查询）的话，性能开销还是相当大的。
方法三：
即嵌套使用 CASE 表达式。CASE 表达式可以写在 SELECT 子句里的聚合函数内部，也可以写在聚合函数外部。
-- 水平展开(3)：嵌套使用CASE 表达式
SELECT name,
CASE WHEN SUM(CASE WHEN course = 'SQL 入门' THEN 1 ELSE NULL END) = 1
THEN '○' ELSE NULL END AS "SQL 入门",
CASE WHEN SUM(CASE WHEN course = 'UNIX 基础' THEN 1 ELSE NULL END) = 1
THEN '○' ELSE NULL END AS "UNIX 基础",
CASE WHEN SUM(CASE WHEN course = 'Java 中级' THEN 1 ELSE NULL END) = 1
THEN '○' ELSE NULL END AS "Java 中级"
FROM Courses
GROUP BY name;
要点：先把 SUM 函数的结果处理成 1 或者NULL ，然后在外层的 CASE 表达式里将 1 转换成○。
另外：如果不使用聚合函数，则会出现多行（如果不使用聚合，那么返回结果的行数会是表 Courses 的行数），如方法一种错误SQL的情况。
二、用外连接进行行列转换(2)（列→行）：汇总重复项于一列
接下来使用外连接进行列转行。
Personnel ：员工子女信息
| employee( 员工 ) | child_1( 孩子 1) | child_2( 孩子 2) | child_3( 孩子 3) |
|---|---|---|---|
| 赤井 | 一郎 | 二郎 | 三郎 |
| 工藤 | 春子 | 夏子 | |
| 铃木 | 夏子 | ||
| 吉田 |
CREATE TABLE Personnel(
id serial PRIMARY KEY,
employee VARCHAR(20),
child_1 VARCHAR(20),
child_2 VARCHAR(20),
child_3 VARCHAR(20)
);
INSERT INTO Personnel(employee,child_1,child_2,child_3)VALUES
('赤井','一郎','二郎','三郎'),
('工藤','春子','夏子',NULL),
('铃木','夏子',NULL,NULL),
('吉田',NULL,NULL,NULL);
将这张表转换成行格式的数据; 这里使用 UNION ALL 来实现
-- 列数据转换成行数据：使用UNION ALL
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 不会排除掉重复的行，所以即使吉田没有孩子，结果里也会出现 3 行相关数据 。
使用UNION虽然可以去重：但也不是想要的结果，铃木有孩子，不应该显示。
根据具体需求，有时需要把没有孩子的吉田也留在表里： 员工子女列表
| employee( 员工 ) | child( 孩子 ) |
|---|---|
| 赤井 | 一郎 |
| 赤井 | 二郎 |
| 赤井 | 三郎 |
| 工藤 | 春子 |
| 工藤 | 夏子 |
| 铃木 | 夏子 |
| 吉田 |
处理方法
- 生成一个存储子女列表的视图
CREATE VIEW Children(child)
AS SELECT child_1 FROM Personnel
UNION
SELECT child_2 FROM Personnel
UNION
SELECT child_3 FROM Personnel;
- 接下来我们以员工列表为主表进行外连接操作
-- 获取员工子女列表的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);
-- 当表 Personnel 里“孩子 1 ～孩子 3”列的名字存在于 Children 视图里时，返回该名字，否则返回 NULL
这里对子女主表和员工表执行了外连接操作，重点在于连接条件是通过 IN 谓词指定的
三、在交叉表里制作嵌套式表侧栏
在生成统计表的工作中，经常会有制作嵌套式表头和表侧栏的需求。例如这道例题：表 TblPop 是一张按照县、年龄层级和性别统计的人口分布表，要求根据表 TblPop 生成交叉表“包含嵌套式表侧栏的统计表”
年龄层级主表：TblAge
| age_class（年龄层级） | age_range（年龄） |
|---|---|
| 1 | 21 岁～ 30 岁 |
| 2 | 31 岁～ 40 岁 |
| 3 | 41 岁～ 50 岁 |
性别主表：TblS
| sex_cd（性别编号） | sex（性别） |
|---|---|
| m | 男 |
| f |
人口分布表：TblPop
| pref_name（县名） | age_class（年龄层级） | sex_cd（性别编号） | population人口） |
|---|---|---|---|
| 秋田 | 1 | m | 400 |
| 秋田 | 3 | m | 1000 |
| 秋田 | 1 | f | 800 |
| 秋田 | 3 | f | 1000 |
| 青森 | 1 | m | 700 |
| 青森 | 1 | f | 500 |
| 青森 | 3 | f | 800 |
| 东京 | 1 | m | 900 |
| 东京 | 1 | f | 1500 |
| 东京 | 3 | f | 1200 |
| 千叶 | 1 | m | 900 |
| 千叶 | 1 | f | 1000 |
| 千叶 | 3 | f | 900 |
包含嵌套式表侧栏的统计表
虽然表 TblPop 中没有一条年龄层级为2 的数据，但是返回结果还是要包含这个年龄层级，固定输出 6 行。生成固定的表侧栏需要用到外连接，但如果要将表侧栏做成嵌套式的，还需要再花点工夫。目标表的侧栏是年龄层级和性别，所以需要使用表TblAge 和表 TblSex 作为主表
--- 年龄层级主表TblAge
CREATE TABLE TblAge (
id serial PRIMARY KEY,
age_class INTEGER,
age_range VARCHAR(20)
);
INSERT INTO TblAge(age_class,age_range)VALUES
(1,'21 岁～ 30 岁'),
(2,'31 岁～ 40 岁'),
(3,'41 岁～ 50 岁');
--- 创建性别主表TblS
CREATE TABLE TblSex(
sex_cd VARCHAR(3),
sex VARCHAR(3)
);
INSERT INTO TblSex(sex_cd,sex)VALUES
('m','男'),
('f','女');
--- 创建人口分布图TblPop
CREATE TABLE TblPop(
id serial PRIMARY KEY,
pref_name VARCHAR(10),
age_class INTEGER,
sex_cd VARCHAR(3),
population INTEGER
);
INSERT INTO TblPop(pref_name,age_class,sex_cd,population)VALUES
('秋田',1,'m',400),
('秋田',3,'m',1000),
('秋田',1,'f',800),
('秋田',3,'f',1000),
('青森',1,'m',700),
('青森',1,'f',500),
('青森',3,'f',800),
('东京',1,'m',1500),
('东京',1,'f',1200),
('东京',3,'f',900),
('千叶',1,'m',900),
('千叶',1,'f',1000),
('千叶',3,'f',900);
思路是以这两张表作为主表进行外连接操作。但是如果像下面的SQL 语句这样简单地进行两次外连接，并不能得到正确的结果
-- 使用外连接生成嵌套式表侧栏：错误的SQL 语句
SELECT MASTER1.age_class AS age_class, MASTER2.sex_cd AS sex_cd, DATA.pop_tohoku AS pop_tohoku, DATA.pop_kanto AS pop_kanto
FROM (SELECT age_class, sex_cd,
SUM(CASE WHEN pref_name IN ('青森', '秋田') THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('东京', '千叶') THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
RIGHT OUTER JOIN TblAge MASTER1 ON MASTER1.age_class = DATA.age_class -- 外连接1：和年龄层级主表进行外连接
RIGHT OUTER JOIN TblSex MASTER2 ON MASTER2.sex_cd = DATA.sex_cd; -- 外连接2 ：和性别主表进行外连接
结果里没有出现年龄层级为 2 的行。技巧是对表 TblAge和表 TblSex 进行交叉连接运算，生成下面这样的笛卡儿积。行数是 3×2 ＝ 6
-- 改造写法
SELECT m1.age_class,m2.sex_cd,d.pop_tohoku,d.pop_kanto
FROM TblAge m1
CROSS JOIN TblSex m2
LEFT JOIN
(SELECT age_class, sex_cd,
SUM(CASE WHEN pref_name IN ('青森', '秋田') THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('东京', '千叶') THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd
) d
ON d.age_class = m1.age_class AND d.sex_cd = m2.sex_cd;
四、作为乘法运算的连接
在 SQL 里，交叉连接相当于乘法运算
下面的商品主表和商品销售历史管理表为例，深入探讨一下
Items
| item_no | item |
|---|---|
| 10 | FD |
| 20 | CD-R |
| 30 | MO |
| 40 | DVD |
SalesHistory
| sale_date | item_no | quantity |
|---|---|---|
| 2007-10-01 | 10 | 4 |
| 2007-10-01 | 20 | 10 |
| 2007-10-01 | 30 | 3 |
| 2007-10-03 | 10 | 32 |
| 2007-10-03 | 30 | 12 |
| 2007-10-04 | 20 | 22 |
| 2007-10-04 | 30 | 7 |
先使用这两张表生成一张统计表，以商品为单位汇总出各自的销量。
SELECT I.item_no, SUM(SH.quantity) AS total_qty
FROM Items I LEFT OUTER JOIN SalesHistory SH ON I.item_no = SH.item_no --一对多的连接
GROUP BY I.item_no;
五、全外连接
Class_A
| id (编号) | name( 名字 ) |
|---|---|
| 1 | 田中 |
| 2 | 铃木 |
| 3 | 伊集院 |
Class_B
| id (编号) | name( 名字 ) |
|---|---|
| 1 | 田中 |
| 2 | 铃木 |
| 4 | 西园寺 |
CREATE TABLE Class_A(
id int4,
name VARCHAR(10)
);
INSERT INTO Class_A(id,name) VALUES
(1,'田中'),
(2,'铃木'),
(3,'伊集院');
CREATE TABLE Class_B(
id int4,
name VARCHAR(10)
);
INSERT INTO Class_B(id,name) VALUES
(1,'田中'),
(2,'铃木'),
(4,'西园寺');
全外连接是能够从这样两张内容不一致的表里，没有遗漏地获取全部信息的方法，所以也可以理解成“把两张表都当作主表来使用”的连接。
-- 全外连接保留全部信息
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;
如果所用的数据库不支持全外连接，可以分别进行左外连接和右外连接，再把两个结果通过 UNION 合并起来
六、用外连接进行集合运算
集合运算符会进行排序，所以可能会带来性能上的问题。因此，了解一下集合运算符的替代方案还是有意义的。
SELECT A.id AS id, A.name AS A_name
FROM Class_A A
LEFT OUTER JOIN Class_B B ON A.id = B.id
WHERE B.name IS NULL;
A表进行左外连接的时候，输出B表的名称。
--用外连接求差集：B － A
SELECT B.id AS id, B.name AS B_name
FROM Class_A A RIGHT OUTER JOIN Class_B B ON A.id = B.id
WHERE A.name IS NULL;
当然，用外连接解决这个问题不太符合外连接原本的设计目的。但是对于不支持差集运算的数据库来说，这也可以作为 NOT IN和NOTEXISTS之外的另一种解法，而且它可能是差集运算中效率最高的，这也是它的优点。
七、用全外连接求异或集
接下来考虑一下如何求两个集合的异或集。SQL 没有定义求异或集的运算符，如果用集合运算符，可以有两种方法。一种是 (A UNION B) EXCEPT (A INTERSECT B)，另一种是(A EXCEPT B) UNION (B EXCEPT A)。
两种方法都比较麻烦，性能开销也会增大
现在请再仔细看一下前面有关全外连接的执行结果，是否得到了灵感呢
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;
-- 像这样改变一下 WHERE 子句的条件，就可以进行各种集合运算
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 NOT NULL AND B.name IS NOT NULL;
现在我们已经求了集合的并集、差集、交集
八、小结
- 生成嵌套式表侧栏时，如果先生成主表的笛卡儿积再进行连接，很容易就可以完成
- 外连接的思想和集合运算很像，使用外连接可以实现各种集合运算。
九、参考
- 《SQL 权威指南（第 4 版）》 第 25 章“SQL 中的数组；第 34 章“集合操作”；27.2.6 节“用集合运算符进行除法”；
- 《SQL 解惑（第 2 版）》行列转换的应用“谜题 14 电话”和“谜题 55 赛马”，以及使用外连接求差集的“谜题 58 间隔——版本二”，
十、习题
习题二：请留意孩子的人数
在“用外连接进行行列转换 (1)（列→行）：汇总重复项于一列”部分，我们求得了以员工为单位的员工子女列表。有了这个列表后，对员工进行一下聚合很容易就可以知道每个员工抚养了几个孩子。求每个员工抚养的孩子的人数
--注意不要使用count(*)
SELECT EMP.employee, COUNT(CHILDREN.child) AS child_cnt
FROM Personnel EMP
LEFT OUTER JOIN Children
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
GROUP BY EMP.employee;
习题三、全外连接和 MERGE 运算符
MERGE`运算符是在 SQL:2003 标准中引入的新特性。因为它可以将两张表的信息汇总到一张表上，所以在需要将分散在多个数据源的数据汇总到一起的场景中能发挥很强大的威力
继续使用A、B 表
Class_A
| id (编号) | name( 名字 ) |
|---|---|
| 1 | 田中 |
| 2 | 铃木 |
| 3 | 伊集院 |
Class_B
| id (编号) | name( 名字 ) |
|---|---|
| 1 | 田中 |
| 2 | 铃木 |
| 4 | 西园寺 |
将表 Class_B 里的数据汇总到表 Class_A 里，目标结果像下表这样。
| id (编号) | name( 名字 ) |
|---|---|
| 1 | 田中 |
| 2 | 铃木 |
| 3 | 伊集院 |
| 4 | 西园寺 |
-- 最简单的方式，直接使用union
SELECT id,name FROM Class_A
UNION
SELECT id,name FROM class_b
ORDER BY id;
处理逻辑是在表 Class_A 中查询表 Class_B 里的“id（编号）”列，如果存在则更新名字，如果不存在则插入。
MERGE 语句主要分为三块。第一块指定合并的表和匹配列，即代码中的 A 块。ON (A.id = B.id) 是匹配条件。
然后对每条记录进行匹配，并根据是否匹配到进行分支处理。本例中，对匹配到的记录执行 UPDATE （B 块），对没有匹配到的记录执行 INSERT （C 块）。执行结果后会得到“A + B”这样存储了完整信息的表（id 为 2 的记录会被覆盖掉，从某种意义上来说也算是信息丢失，但是这里所说的“完整”强调的是“没有缺失的 id ”）
在无法使用 MERGE 语句的环境中，我们可以使用 UPDATE 和INSERT分两次处理，或者使用外连接后将结果 INSERT 到另一张表中
扫一扫
1320
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
