1-5 外连接的用法_外连接forgine的使用-CSDN博客

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

实现这种效果
在这里插入图片描述外连接形式. 这种做法具有比较直观和易于理解的优点，但是因为大量用到了内嵌视图和连接操作，代码会显得很臃肿。而且，随着表头列数的增加，性能也会恶化。

select a.`name`,
case when b.name is not null then 'o' ELSE null end as 'SQL入门',
case when c.name is not null then 'o' ELSE null end as 'UNIX基础',
case when d.name is not null then 'o' ELSE null end as 'Java中级'
from (select DISTINCT name from courses) a
LEFT JOIN (select * from courses where course='SQL入门') b on a.name=b.name
LEFT JOIN (select * from courses where course='UNIX基础') c on a.name=c.name
LEFT JOIN (select * from courses where course='Java中级') d on a.name=d.name

一般情况下，外连接都可以用标量子查询替代。因此可以像下面这样写。这种做法不仅利于应对需求变更，对于需要动态生成 SQL 的系统也是很有好处的。缺点是性能不太好，目前在 SELECT 子句中使用标量子查询（或者关联子查询）的话，性能开销还是相当大的。

select a.name,
(select 'o' from courses where courses.`name`=a.name and courses.course='SQL入门') 'SQL入门',
(select 'o' from courses where courses.`name`=a.name and courses.course='UNIX基础') 'UNIX基础',
(select 'o' from courses where courses.`name`=a.name and courses.course='Java中级') 'Java中级'
from (select DISTINCT name from courses) a

使用case表达式

select name,
case when sum(case when course = 'sql入门' then 1 else null end)=1 then 'o' else null end, 
case when sum(case when course = 'UNIX基础' then 1 else null end)=1 then 'o' else null end, 
case when sum(case when course = 'Java中级' then 1 else null end)=1 then 'o' else null end
from courses
group by name

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

在这里插入图片描述使用union all，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;

也许我们想要另一种数据：如果有孩子则显示所有与孩子有关的数据，没有就显示一条孩子为空的记录。

CREATE VIEW Children(child)
AS SELECT child_1 FROM Personnel
UNION
SELECT child_2 FROM Personnel
UNION
SELECT child_3 FROM Personnel;

-- 获取员工子女列表的SQL 语句（没有孩子的员工也要输出）,这条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_class AS age_class,
MASTER.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM 
-- 使用交叉连接生成两张主表的笛卡儿积
(SELECT age_class, 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_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 语句还是有些问题的。比如临时视图 SH 的数据需要临时存储在内存里，还有就是虽然通过聚合将 item_no 变成了主键，但是 SH 上却不存在主键索引，因此我们也就无法利用索引优化查询。

SELECT I.item_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;

先进行一对多的连接再聚合。当连接操作的双方是一对多关系时，结果的行数并不会增加。大概大家首先想到的都会是这种方法。这种做法代码更简洁，而且没有使用临时视图，所以性能也会有所改善。

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;

外连接

本章节使用的表
在这里插入图片描述

全外连接

mysql不支持全外连接，但是可以通过union左连接和右连接来达到全外连接的效果。例如:

-- 数据库不支持全外连接时的替代方案
SELECT A.id AS id, A.name, B.name
FROM Class_A A LEFT OUTER JOIN Class_B B ON A.id = B.id
UNION
SELECT B.id AS id, A.name, B.name
FROM Class_A A RIGHT OUTER JOIN Class_B B ON A.id = B.id;

求差集：A - B

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;

求差集：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;

求异或集

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;