SQL进阶 | 外连接

概述

        外连接的进阶用法在行列转换中比较有优势，往往存在需要把数据库中的格式转换成报表格式，但是SQL仅仅只是查询数据的语言，格式转换并不是原本的用途。

全外连接

        标准 SQL 里定义了外连接的三种类型，如下所示。

• 左外连接（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_a15 a full outer join class_b15 b
	on a.id = b.id;

-- 数据库不支持全连接
-- 使用左连接与右连接的并集
 SELECT A.id AS id,
 A.name AS A_name,
 B.name AS B_name
 FROM Class_A15 A left JOIN Class_B15 B
 ON A.id = B.id 
 union
 SELECT B.id AS id,
 A.name AS A_name,
 B.name AS B_name
 FROM Class_A15 A right JOIN Class_B15 B
 ON A.id = B.id;

用外连接进行集合运算

用外连接求差集 ：A － B

 -- 用外连接求差集 ：  A - B
 select a.id as id, a.name as a_name
 from class_a15 a left join class_b15 b
	on a.id = b.id
	where b.name is null;

用外连接求差集 ：B－ A

 -- 用外连接求差集： B - A
 select b.id as id,b.name as b_name
 from class_a15 a right join class_b15 b
	on a.id = b.id
where a.name is null;

用全外连接求异或集 

select a.id as id ,a.name as name
from class_a15 a left join class_b15 b on a.id = b.id
where a.name is null or b.name is null
union
select b.id as id ,b.name as name
from class_a15 a right join class_b15 b on a.id = b.id
where a.name is null or b.name is null

用外连接进行行列转换 (1)（行→列）：制作交叉表

        这里有一张用于管理员工学习过的培训课程的表，如下所示。

        利用上面这张表生成下面这样一张交叉表（“课程学习记录一览表”）。○表示已学习过，NULL 表示尚未学习。

-- 使用外连接
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 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 个子集。C0 包含了全部员工，起到了“员工主表”的作用。C1 ～ C3 是每个课程的学习者的集合。这里以 C0 为主表，依次对 C1 ～ C3 进行外连接操作。如果某位员工学习过某个课程，则相应的课程列会出现他的姓名，否则为 NULL。最后，通过 CASE 表达式将课程列中员工的姓名转换为○就算完成了。

        但是因为大量用到了内嵌视图和连接操作，代码会显得很臃肿。而且，随着表头列数的增加，性能也会恶化。

-- 水平展开2：使用标量子查询
select c0.name,
	(select 'O' from courses c1 where course = 'SQL入门' and c1.name = c0.name) as "SQL入门",
	(select 'O' from courses c2 where course = 'UNIX基础' and c2.name = c0.name) as 'UNIX基础',
	(select 'O' from courses c3 where course = 'Java中级' and c3.name = c0.name) as "Java中级"
from (select distinct name from courses) c0; -- 这里的c0是表侧栏

-- 水平展开 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)（列→行）：汇总重复项于一列

        假设存在下面这样一张表。

 

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 行相关数据。所以对结果再次优化。先创建一个视图，保存所有的子女列表。然后用员工表作为主表与该视图进行外连接，当员工表中的孩子1-3存在于视图中，返回该名字，否则返回null。

-- 生成一个存储子女列表的视图
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);

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

        在生成统计表的工作中，经常会有制作嵌套式表头和表侧栏的需求。例如这道例题：表 TblPop 是一张按照县、年龄层级和性别统计的人口分布表，要求根据表 TblPop 生成交叉表“包含嵌套式表侧栏的统计表”。

使用交叉连接对tblage和tblsex生成笛卡尔积作为主表，和tblpop进行外连接按照年龄区间和性别分组统计人数和。

-- 使用外连接生成嵌套式表侧栏
select master.age_range as age_class,
				master.sex as sex_acd,
				data.pop_tohoku as pop_tohoku,
				data.pop_kanto as pop_kanto
from (select age_class,age_range,sex_cd,sex
			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_tohoku,
										sum(case when pref_name in ('东京','千叶') then population else null end) as pop_kanto
									from tblpop
									group by age_class,sex_cd) data
	on master.age_class = data.age_class and master.sex_cd = data.sex_cd;	

总结

• SQL 不是用来生成报表的语言，所以不建议用它来进行格式转换。
• 必要时考虑用外连接或 CASE 表达式来解决问题。
• 生成嵌套式表侧栏时，如果先生成主表的笛卡儿积再进行连接，很容易就可以完成。
• 从行数来看，表连接可以看成乘法。因此，当表之间是一对多的关系时，连接后行数不会增加。
• 外连接的思想和集合运算很像，使用外连接可以实现各种集合运算。