1-5 外连接的用法-SQL进阶教程

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

-- 水平展开求交叉表(1)：使用外连接
select C0.name,
(case when C1.name is not null then 'o' else null end) 'SQL入门',
(case when C2.name is not null then 'o' else null end) 'UNIX基础',
(case when C3.name is not null then 'o' else null end) 'Java中级'
from (select distinct name from Courses) C0
left join (select name from Courses where course='SQL入门') C1
on C0.name=C1.name
left join (select name from Courses where course='UNIX基础') C2
on C0.name=C2.name
left join (select name from Courses where course='Java中级') C3
on C0.name=C3.name;

-- 水平展开(2)：使用标量子查询
select C0.name,
(select 'o' from Courses C1 where course='SQL入门' and C1.name=C0.name) 'SQL入门',
(select 'o' from Courses C2 where course='UNIX基础' and C2.name=C0.name) 'UNIX基础',
(select 'o' from Courses C3 where course='Java中级' and C3.name=C0.name) 'Java中级'
from (select distinct name from Courses) C0;

-- 水平展开(3)：嵌套使用case表达式
select name,
(case when sum(case when course='SQL入门' then 1 else null end)=1 then 'o' else null end) 'SQL入门',
(case when sum(case when course='UNIX基础' then 1 else null end)=1 then 'o' else null end) 'UNIX基础',
(case when sum(case when course='Java中级' then 1 else null end)=1 then 'o' else null end) 'Java中级'
from Courses
group by name;

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

-- 列数据转换成行数据：使用union all
select empolyee,child_1 child from Personnel
union all
select empolyee,child_2 child from Personnel
union all
select empolyee,child_3 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语句(没有孩子的员工也要输出)
select P.employee,C.child
from Personnel P
left join Children C
on C.child in(P.child_1,P.child_2,P.child_3);

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

-- 使用外连接生成嵌套式表侧栏
select MASTER.age_class age_class,MASTER.sex_cd sex_cd,
DATA.pop_tohoku pop_tohoku,DATA.pop_kanto pop_kanto from(
	select age_class,sex_cd
	from TblAge
	cross join TblSex
) MASTER -- 使用交叉连接生成两张主表
left join(
	select age_class,sex_cd,
	sum(case when pref_name in('青森','秋田') then population else null end) pop_tohoku,
	 sum(case when pref_name in('东京','千叶') then population else null end) 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;

作为乘法运算的连接

-- 解答(1)：通过在连接前聚合来创建一对一的关系
select I.item_no,SH.total_qty
from Items_I
left join(
	select item_no,sum(quantity) total_qty
	from SalesHistory
	group by item_no
) SH
on I.item_no=SH.item_no;

-- 解答(2) ：先进行一对多的连接再聚合
select I.item_no,sum(SH.quantity) total_qty
from Items I
left join SalesHistory SH
on I.item_no=SH.item_no -- 一对多的连接
group by I.item_no;

全外连接

全外连接：能够从两张内容不一致的表里没有遗漏地获取全部信息
也可以理解成“把两张表都当作主表来使用”

-- 全外连接保留全部信息
select coalesce(A.id,B.id) id,A.name A_name,B.name B_name
from Class_A A
full join Class_B B
on A.id=B.id;

-- 数据库不支持全外连接时的替代方案
select A.id id,A.name,B.name
from Class_A A
left join Class_B B
on A.id=B.id
union
select B.id id,A.name,B.name
from Class_A A
right join Class_B B
on A.id=B.id;
/*
内连接相当于求集合的积(intersect,也称交集),全外连接相当于求集合的和(union,也称并集)
*/

用外连接进行集合运算

-- 用外连接求差集：A－B
select A.id id,A.name A_name
from Class_A A
left join Class_B B
on A.id=B.id
where B.name is null;

-- 用外连接求差集：B－A
select B.id id,B.name B_name
from Class_A A
right join Class_B B
on A.id=B.id
where A.name is null;

用全外连接求异或集

select coalesce(A.id,B.id) id,coalesce(A.name,B.name) name
from Class_A A
full join Class_B B
on A.id=B.id
where A.name is null
or B.name is null;

-- 用外连接进行关系除法运算：差集的应用
select distinct shop
from ShopItems SI1
where not exists(
	select I.item
	from Items I
	left join ShopItems SI2
	on I.item=SI2.item
	and SI1.shop=SI2.shop
	where SI2.item is null
);