读SQL进阶教程笔记06_外连接

1. SQL的弱点

1.1. SQL语句的执行结果转换为想要的格式

• 1.1.1. 格式转换
  
• 1.1.2. SQL语言本来就不是为了这个目的而出现的
  
• 1.1.3. SQL终究也只是主要用于查询数据的语言而已
  

1.2. 生成报表的功能

• 1.2.1. 窗口函数
  

1.3. SQL不是用来生成报表的语言，所以不建议用它来进行格式转换

• 1.3.1. 必要时考虑用外连接或CASE表达式来解决问题
  

2. 制作交叉表（行→列）

2.1. 示例

• 2.1.1.
  

• 2.1.1.1. --水平展开求交叉表(1)：使用外连接
  

1.    SELECT C0.name,
2.      CASE WHEN C1.name IS NOT NULL THEN'○'ELSE NULL END AS "SQL入门",
3.      CASE WHEN C2.name IS NOT NULL THEN'○'ELSE NULL END AS "UNIX基础",
4.      CASE WHEN C3.name IS NOT NULL THEN'○'ELSE NULL END AS "Java中级"
5.      FROM  (SELECT DISTINCT name FROM  Courses) C0   --这里的C0是侧栏
6.      LEFT OUTER JOIN
7.        (SELECT name FROM Courses WHERE course = 'SQL入门’) C1
8.        ON  C0.name = C1.name
9.          LEFT OUTER JOIN
10.            (SELECT name FROM Courses WHERE course = 'UNIX基础’) C2
11.            ON  C0.name = C2.name
12.        LEFT OUTER JOIN
13.          (SELECT name FROM Courses WHERE course = 'Java中级’) C3
14.          ON  C0.name = C3.name;

复制代码

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

  2.1.1.2.1. 需要增加或者减少课程时，只修改SELECT子句即可，代码修改起来比较简单
  

  2.1.1.2.2. 利于应对需求变更，对于需要动态生成SQL的系统也是很有好处的
  

  2.1.1.2.3. 性能不太好
  

  
  
• 2.1.1.3. --水平展开(2)：使用标量子查询
  

1.    SELECT C0.name,
2.          (SELECT '○'
3.              FROM Courses C1
4.            WHERE course = 'SQL入门’
5.              AND C1.name = C0.name) AS "SQL入门",
6.          (SELECT '○'
7.              FROM Courses C2
8.            WHERE course = 'UNIX基础’
9.                AND C2.name = C0.name) AS "UNIX基础",
10.            (SELECT '○'
11.               FROM Courses C3
12.              WHERE course = 'Java中级’
13.                AND C3.name = C0.name) AS "Java中级"
14.        FROM (SELECT DISTINCT name FROM Courses) C0;  --这里的C0是表侧栏

复制代码

• 2.1.1.4. 嵌套使用CASE表达式
  

  2.1.1.4.1. CASE表达式可以写在SELECT子句里的聚合函数内部，也可以写在聚合函数外部
  

  2.1.1.4.2. 其实在SELECT子句里，聚合函数的执行结果也是标量值，因此可以像常量和普通列一样使用
  

  2.1.1.4.3. 和标量子查询的做法一样简洁，也能灵活地应对需求变更
  

  
  
• 2.1.1.5. --水平展开(3)：嵌套使用CASE表达式
  

1.    SELECT name,
2.      CASE WHEN SUM(CASE WHEN course = 'SQL入门’THEN 1 ELSE NULL END) = 1
3.          THEN'○'ELSE NULL END AS "SQL入门",
4.      CASE WHEN SUM(CASE WHEN course = 'UNIX基础’THEN 1 ELSE NULL END) = 1
5.          THEN'○'ELSE NULL END AS "UNIX基础",
6.      CASE WHEN SUM(CASE WHEN course = 'Java中级’THEN 1 ELSE NULL END) = 1
7.          THEN'○'ELSE NULL END AS "Java中级"
8.      FROM Courses
9.     GROUP BY name;

复制代码

3. 汇总重复项于一列（列→行）

3.1. 示例

• 3.1.1.
  

• 3.1.1.1. --列数据转换成行数据：使用UNION ALL
  

1.    SELECT employee, child_1 AS child FROM Personnel
2.    UNION ALL
3.    SELECT employee, child_2 AS child FROM Personnel
4.    UNION ALL
5.    SELECT employee, child_3 AS child FROM Personnel;

复制代码

• 3.1.1.2.  视图
  

1. CREATE VIEW Children(child)
2.   AS SELECT child_1 FROM Personnel
3.     UNION
4.     SELECT child_2 FROM Personnel
5.     UNION
6.     SELECT child_3 FROM Personnel;
7.   child
8. -----
9. 一郎
10. 二郎
11. 三郎
12. 春子
13. 夏子

复制代码

3.1.1.2.1. --获取员工子女列表的SQL语句（没有孩子的员工也要输出）

1.    SELECT EMP.employee, CHILDREN.child
2.      FROM Personnel EMP
3.          LEFT OUTER JOIN Children
4.            ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);

复制代码

4. 制作嵌套式表侧栏

4.1. 示例

• 4.1.1.
  

• 4.1.2.
  

• 4.1.3. 结果
  
  
  
  • 4.1.3.1.
    
  
  

• 4.1.4. --使用外连接生成嵌套式表侧栏：错误的SQL语句
  

1. SELECT  MASTER1.age_class AS age_class,
2.         MASTER2.sex_cd    AS sex_cd,
3.         DATA.pop_tohoku   AS pop_tohoku,
4.         DATA.pop_kanto    AS pop_kanto
5.   FROM (SELECT age_class, sex_cd,
6.             SUM(CASE WHEN pref_name IN (’青森’, ’秋田’)
7.                       THEN population ELSE NULL END) AS pop_tohoku,
8.             SUM(CASE WHEN pref_name IN (’东京’, ’千叶’)
9.                       THEN population ELSE NULL END) AS pop_kanto
10.           FROM TblPop
11.         GROUP BY age_class, sex_cd) DATA
12.         RIGHT OUTER JOIN TblAge MASTER1--外连接1：和年龄层级主表进行外连接
13.             ON MASTER1.age_class = DATA.age_class
14.         RIGHT OUTER JOIN TblSex MASTER2--外连接2：和性别主表进行外连接
15.             ON MASTER2.sex_cd = DATA.sex_cd;

复制代码

• 4.1.4.1. --停在第1个外连接处时：结果里包含年龄层级为2的数据
  

1. SELECT MASTER1.age_class AS age_class,
2.       DATA.sex_cd        AS sex_cd,
3.       DATA.pop_tohoku   AS pop_tohoku,
4.       DATA.pop_kanto    AS pop_kanto
5.   FROM (SELECT age_class, sex_cd,
6.             SUM(CASE WHEN pref_name IN (’青森’, ’秋田’)
7.                       THEN population ELSE NULL END) AS pop_tohoku,
8.             SUM(CASE WHEN pref_name IN (’东京’, ’千叶’)
9.                       THEN population ELSE NULL END) AS pop_kanto
10.             FROM TblPop
11.            GROUP BY age_class, sex_cd) DATA
12.            RIGHT OUTER JOIN TblAge MASTER1
13.               ON MASTER1.age_class = DATA.age_class;

复制代码

• 4.1.5. 如果不允许进行两次外连接，那么调整成一次就可以了
  
• 4.1.6. 对于不支持CROSS JOIN语句的数据库，可以像FROM TblAge,TblSex这样不指定连接条件，把需要连接的表写在一起，其效果与交叉连接一样
  
• 4.1.7. 如果先生成主表的笛卡儿积再进行连接，很容易就可以完成
  
• 4.1.8. --使用外连接生成嵌套式表侧栏：正确的SQL语句
  

1.    SELECT MASTER.age_class AS age_class,
2.          MASTER.sex_cd    AS sex_cd,
3.          DATA.pop_tohoku  AS pop_tohoku,
4.          DATA.pop_kanto   AS pop_kanto
5.     FROM (SELECT age_class, sex_cd
6.            FROM TblAge CROSS JOIN TblSex ) MASTER  --使用交叉连接生成两张主表的笛卡儿积
7.        LEFT OUTER JOIN
8.          (SELECT age_class, sex_cd,
9.                SUM(CASE WHEN pref_name IN (’青森’, ’秋田’)
10.                        THEN population ELSE NULL END) AS pop_tohoku,
11.                SUM(CASE WHEN pref_name IN (’东京’, ’千叶’)
12.                        THEN population ELSE NULL END) AS pop_kanto
13.            FROM TblPop
14.            GROUP BY age_class, sex_cd) DATA
15.              ON  MASTER.age_class = DATA.age_class
16.            AND  MASTER.sex_cd    = DATA.sex_cd;

复制代码

5. 作为乘法运算的连接

5.1. 示例

• 5.1.1.
  

• 5.1.2. --解答(1)：通过在连接前聚合来创建一对一的关系
  

1.    SELECT I.item_no, SH.total_qty
2.      FROM Items I LEFT OUTER JOIN
3.            (SELECT item_no, SUM(quantity) AS total_qty
4.                FROM SalesHistory
5.              GROUP BY item_no) SH
6.        ON I.item_no = SH.item_no;

复制代码

• 5.1.2.1. 以商品编号为主键的临时视图
  
• 5.1.2.2. 无法利用索引优化查询
  
• 5.1.3. --解答(2)：先进行一对多的连接再聚合
  

1.    SELECT I.item_no, SUM(SH.quantity) AS total_qty
2.      FROM Items I LEFT OUTER JOIN SalesHistory SH
3.        ON I.item_no = SH.item_no  一对多的连接
4.     GROUP BY I.item_no;

复制代码

• 5.1.3.1. 代码更简洁
  
• 5.1.3.2. 没有使用临时视图，所以性能也会有所改善
  

5.2. 从行数来看，表连接可以看成乘法。因此，当表之间是一对多的关系时，连接后行数不会增加
6. 全外连接

6.1. FULL OUTER JOIN
6.2. 相当于求集合的和（UNION，也称并集）

• 6.2.1. 内连接相当于求集合的积（INTERSECT，也称交集）
  

6.3. 示例

• 6.3.1. --全外连接保留全部信息
  

1.    SELECT COALESCE(A.id, B.id) AS id,
2.          A.name AS A_name,
3.          B.name AS B_name
4.      FROM Class_A  A  FULL OUTER JOIN Class_B  B
5.        ON A.id = B.id;

复制代码

• 6.3.1.1. --数据库不支持全外连接时的替代方案
  

1.    SELECT A.id AS id, A.name, B.name
2.      FROM Class_A  A   LEFT OUTER JOIN Class_B  B
3.        ON A.id = B.id
4.    UNION
5.    SELECT B.id AS id, A.name, B.name
6.      FROM Class_A  A  RIGHT OUTER JOIN Class_B  B
7.        ON A.id = B.id;

复制代码

6.3.1.1.1. 分别进行左外连接和右外连接，再把两个结果通过UNION合并起来

6.4. COALESCE是SQL的标准函数

• 6.4.1. 可以接受多个参数，功能是返回第一个非NULL的参数
  

6.5. 外连接的思想和集合运算很像，使用外连接可以实现各种集合运算
7. 用外连接求差集：B－A

7.1. 示例

• 7.1.1.
  

1. SELECT B.id AS id, B.name AS B_name
3.      FROM Class_A  A  RIGHT OUTER JOIN Class_B B
4.        ON A.id = B.id
5.     WHERE A.name IS NULL;

复制代码

7.2. 可以作为NOT IN和NOT EXISTS之外的另一种解法
7.3. 可能是差集运算中效率最高的
8. 用全外连接求异或集

8.1. SQL没有定义求异或集的运算符
8.2. 用集合运算符

• 8.2.1. (A UNION B) EXCEPT (A INTERSECT B)
  
• 8.2.2. (A EXCEPT B) UNION (B EXCEPT A)
  
• 8.2.3. 性能开销大
  

8.3. 示例

• 8.3.1.
  

1. SELECT COALESCE(A.id, B.id) AS id,
2.          COALESCE(A.name , B.name ) AS name
3.      FROM Class_A  A  FULL OUTER JOIN Class_B  B
4.        ON A.id = B.id
5.     WHERE A.name IS NULL
6.        OR B.name IS NULL;

复制代码

9. 用外连接进行关系除法

9.1. 示例

• 9.1.1. --用外连接进行关系除法运算：差集的应用
  

1.    SELECT DISTINCT shop
2.      FROM ShopItems SI1
3.    WHERE NOT EXISTS
4.          (SELECT I.item
5.            FROM Items I LEFT OUTER JOIN ShopItems SI2
6.              ON I.item   = SI2.item
7.              AND SI1.shop = SI2.shop
8.            WHERE SI2.item IS NULL) ;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！