SQL 外连接

 1 外连接

外连接是一种用于结合两个或多个表的方式，返回至少一个表中的所有记录。

表 外连接的三种类型

1.1 实践

1.1.1 行->列的转换：制作交叉表

图 课程信息t_courses表及期望输出

需求：O表示已学过，NULL表示尚未学习，利用课程表生成上面的交叉表。

-- 左连接
SELECT c1.name,
CASE WHEN c2.`name` IS NULL THEN NULL ELSE 'O' END AS 'SQL入门',
CASE WHEN c3.`name` IS NULL THEN NULL ELSE 'O' END AS 'UNIX基础',
CASE WHEN c4.`name` IS NULL THEN NULL ELSE 'O' END AS 'Java中级'
FROM (SELECT DISTINCT `name` FROM t_courses) c1
LEFT JOIN (SELECT `name` FROM t_courses WHERE course = 'SQL入门') c2 ON c1.name = c2.name 
LEFT JOIN (SELECT `name` FROM t_courses WHERE course = 'UNIX基础') c3 ON c1.name = c3.name 
LEFT JOIN (SELECT `name` FROM t_courses WHERE course = 'Java中级') c4 ON c1.name = c4.name 

上面代码比较直观和易于理解，但是大量用到了内嵌视图和连接操作，代码显得很臃肿。而且随着表头列数的增加，性能也会恶化。

一般情况下，外连接都可以用标量子查询替代。

-- 标量子查询
SELECT c.name,
(SELECT 'O' FROM t_courses WHERE `name` = c.name AND course = 'SQL入门') AS 'SQL入门',
(SELECT 'O' FROM t_courses WHERE `name` = c.name AND course = 'UNIX基础') AS 'UNIX基础',
(SELECT 'O' FROM t_courses WHERE `name` = c.name AND course = 'Java中级') AS 'Java中级'
FROM (SELECT DISTINCT `name` FROM t_courses) c; 

标量子查询（或者关联子查询），性能开销还是相当大的，因为其是针对SELECT返回的每一行来执行的。

-- 嵌套使用CASE表达式 
SELECT `name`,
CASE WHEN SUM(CASE WHEN course = 'SQL入门' THEN 1 ELSE 0 END) = 1 THEN 'O' else NULL END AS 'SQL入门',
CASE WHEN SUM(CASE WHEN course = 'UNIX基础' THEN 1 ELSE 0 END) = 1 THEN 'O' else NULL END AS 'UNIX基础',
CASE WHEN SUM(CASE WHEN course = 'Java中级' THEN 1 ELSE 0 END) = 1 THEN 'O' else NULL END AS 'Java中级'
FROM t_courses 
GROUP BY `name`;

1.1.2 列 -> 行的转换：汇总重复项于一列

图 员工个人信息t_personnel表及期望输出

-- 将列数据转换成行数据，使用UNION
SELECT employee,child_1 as child
FROM t_personnel
UNION
SELECT employee,child_2
FROM t_personnel
UNION
SELECT employee,child_3
FROM t_personnel;

表 使用UNION后的效果

但是像“铃木 NULL、工藤 NULL”这样的数据不希望输出，而”宫田 NULL”这样的数据要输出（他名下没有孩子，但是输出报表的时候，不能丢失这个员工信息）。

-- LEFT JOIN ... ON ...IN... 
SELECT p.employee,c.child
FROM t_personnel p
left join (
	SELECT *
	FROM (
		SELECT child_1 AS child
		FROM t_personnel
		UNION 
		SELECT child_2 AS child
		FROM t_personnel
		UNION
		SELECT child_3 AS child
		FROM t_personnel
	) temp
	WHERE child IS NOT NULL
) c ON c.child IN (p.child_1,p.child_2,p.child_3);

这样用了左连接，同时连接条件用了“IN”。

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

图 年龄段t_age_class、性别类别t_sex、人口t_population表及期望输出

SELECT a.age_range,s.sex,p.area1 AS '东北',p.area2 AS '关东'
FROM 
(
SELECT age_class,sex_cd,
SUM(CASE WHEN area IN ('秋田','青森') THEN population ELSE NULL END) AS area1,
SUM(CASE WHEN area IN ('东京','千叶') THEN population ELSE NULL END) AS area2
FROM t_population
GROUP BY age_class,sex_cd
) p 
RIGHT JOIN t_age_class a ON a.age_class = p.age_class
RIGHT JOIN t_sex s ON s.sex_cd = p.sex_cd;

上面代码会导致31岁～40岁这个年龄段丢失。

图 输出结果

应当将t_age_class 与 t_sex 先进行连接。

SELECT a.age_range,s.sex,
SUM(CASE WHEN p.area IN ('秋田','青森') THEN p.population ELSE NULL END) AS '东北',
SUM(CASE WHEN p.area IN ('东京','千叶') THEN p.population ELSE NULL END) AS '关东'
FROM t_age_class a 
CROSS JOIN t_sex s
LEFT JOIN t_population p ON p.age_class = a.age_class AND p.sex_cd = s.sex_cd
GROUP BY a.age_class,s.sex;

1.1.4 作为乘法运算的连接

图 商品信息t_items、商品销量信息t_sales_history表及期望输出

SELECT i.item_no,SUM(quantity) AS quantity
FROM t_items i 
LEFT JOIN t_sales_history s ON s.item_no = i.item_no
GROUP BY i.item_no;

1.1.5 将两张表汇总到一张表

图 两张待融合的表

需求：将t_table_2 的数据全部融合到t_table_1，要求，id相同，则对t_table_1进行更新，否则进行插入。

图 融合后的t_table_1表

-- t_table_1 的主键为id
INSERT INTO t_table_1(id,`name`) 
SELECT id,`name`
FROM t_table_2
ON DUPLICATE KEY 
UPDATE `name` = VALUES(`name`);