SQL的进阶之路05：外连接

SQL外连接是常用的格式转换方式，分为全连接，左连接，右连接。本节专门对外连接的特性进行讲解！

一，利用外联接进行（行->列）

 利用这张表生成下面这样格式的表格：

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 Course)  C0 
LEFT JOIN (SELECT DISTINCT name FROM COURSE  WHERE COURSE = 'SQL入门') C1 ON C0.name = C1.name 
LEFT JOIN (SELECT DISTINCT name FROM COURSE  WHERE COURSE = 'UNIX基础') C2 ON C0.name = C2.name 
LEFT JOIN (SELECT DISTINCT name FROM COURSE  WHERE COURSE = 'Java入门')  C3 ON C0.name = C3.name 	

-分析思路：侧栏我们可以使用主表来生成，其他显示成绩的可以使用外连接和CASE表达式， 可以理解通过主表生成4个C0~C3四个子集来完成此次操作。

C0:包含了所有的学生，起到一个学生主表作用，

C1~C3:每个表都是一个课程的集合，然后分别用外连接操作，通过学生来进行关联课程的3个集合。（注意外连接当为右边为NULL时右边显示NULL）。如果某位 学生学习过某个课程，则相应的课程列会出现他的姓名，否则为 NULL 。最后通过CASE表达式将课程列中员工的姓名转为O即可。

存在问题：当课程很多时我们再次使用这个结构的SQL，会非常的冗余，代码重复太高。 

水平展开1：关联子查询

SELECT c0.name,
(select 'o' from course c1 where c1.name = c0.name and c1.course = 'SQL入门') as 'sql入门',
(select 'o' from course c2 where c2.name = c0.name and c2.course = 'UNIX基础') as 'UNIX基础',
(select 'o' from course c3 where c3.name = c0.name and c3.course = 'Java入门') as 'java'
FROM (SELECT DISTINCT name FROM Course)  C0 

分析思路：使用主表生成表头，使用关联子查询来进行条件判定生成结果课程内容。

存在问题：虽然SQL结构简单了而且增加或者删除也方便，增加PHP课程著需要增加SELECT子查询即可。但是这样的性能并不好。

水平展开2：嵌套使用CASE表达式

SELECT NAME,
CASE WHEN SUM( CASE WHEN COURSE = 'sql入门' THEN 1 ELSE 0 END) = 1 THEN '0' ELSE NULL END AS 'SQL',
CASE WHEN SUM( CASE WHEN COURSE = 'UNIX基础' THEN 1 ELSE 0 END) = 1 THEN '0' ELSE NULL END AS 'JAVA',
CASE WHEN SUM( CASE WHEN COURSE = 'Java入门' THEN 1 ELSE 0 END) = 1 THEN '0' ELSE NULL END AS 'UNIX'
from course group by name;

思路分析 ：CASE 表达式可 以写在 SELECT 子句里的聚合函数内部，也可以写在聚合函数外部 （请参考 1-1 节）。这里，我们先把 SUM 函数的结果处理成 1 或者 NULL ，然后在外层的 CASE 表达式里将 1 转换成○。如果不使用聚合，那么返回结果的行数会是表 Courses 的行数，所以 这里以参加培训课程的员工为单位进行聚合。这种做法和标量子查询 的做法一样简洁，也能灵活地应对需求变更。关于将聚合函数的返回 值用于条件判断的写法，如果大家不习惯，可能会有点疑惑。但是， 其实在 SELECT 子句里，聚合函数的执行结果也是标量值，因此可以 像常量和普通列一样使用。如果明白这点，就不难理解了。

二，利用外联接进行（行->列）

 假设一个人有几个小孩那么我们如何将其提取为下图这种格式呢？、

 如果转为上图数据格式我们可以借助union all来完成此次数据格式；

select employee,child1 from perssonnel
union all
select employee,child2 from perssonnel
union all
select employee,child3 from perssonnel

问题：UNION ALL 不会排除重复的行，即使吉田没有孩子也会将其列出来，所以最好将其排除。

-- 当然我们优先讲解外连接
-- 如果我们有一个孩子名称的备用表，那么可以直接使用
-- 孩子表为Child
select per.employee,c.child
from perssonnel per  left join child c
on c.child in (per.child1,per.child2,per.child3)


-- 可以增加WHERE 条件
select employee,child1 from perssonnel where child1 is not null
union all
select employee,child2 from perssonnel where child2 is not null
union all
select employee,child3 from perssonnel where child3 is not null;

思路分析：一般来说公司这种情况都有码表，通过外连接，但是ON条件是IN来指定所以当存在码表中就会返回名字，否则返回NULL。

三，作为乘法连接运算

假设让以商品为单位汇总各自的销量，请看下图：

 如何使用外连接来形成这种数据格式？

-- 大家可能第一时间想到这中方式
-- 先连接在聚合
select i.item ,sum(s.number)
from item i left join shopitems s 
on i.item = s.item
group by i.item


-- 也可以使用先子查询在连接
select i.item ,s.number
from item i left join (select item ,sum(number) as number  from shopitems GROUP BY item)s 
on i.item = s.item

 思路分析：第一种方式我们是先连接在使用聚合函数搭配分组共同使用，第二种连接先使用子查询查询出结果在进行连接。两种方式其实都可以，只是第一种没有视图更加简单。

练习题

1.求每个员工抚养的孩子的人数

 

select s.employee,count(s.child)
from(
select employee,child1 as child  from perssonnel
union all
select employee,child2 as child from perssonnel
union all
select employee,child3 as child from perssonnel

) s group by s.employee

---

划重点：

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