SQL05外连接的使用

前言

本文是《SQL进阶教程》阅读笔记的第5篇，主要讲述外连接在以下几个方面的内容：

• 行列转换
• 集合运算

正文

01 行转列

给定数据如下表所示：

将其转为下表的格式：

我们可以使用外连接实现这个转换。

SELECT c0.name ,
       CASE WHEN c1.name IS NULL THEN '!'
            ELSE ''
             END AS c11 ,
       CASE WHEN c2.name IS NULL THEN '!'
            ELSE ''
             END AS c22 ,
       CASE WHEN c3.name IS NULL THEN '!'
            ELSE ''
             END AS c33
  FROM (
        SELECT DISTINCT name
          FROM courses
       ) c0
  LEFT JOIN (
        SELECT DISTINCT name
          FROM courses
         WHERE course='SQL入门'
       ) c1
    ON c0.name=c1.name
  LEFT JOIN (
        SELECT DISTINCT name
          FROM courses
         WHERE course='Java中级'
       ) c2
    ON c0.name=c2.name
  LEFT JOIN (
        SELECT DISTINCT name
          FROM courses
         WHERE course='UNIX基础'
       ) c3
    ON c0.name=c3.name

02 列转行

SELECT employee,
       child_1
  FROM personnel
UNION ALL SELECT employee,
       child_2
  FROM personnel
UNION ALL SELECT employee,
       child_3
  FROM personnel

03 作为乘法的连接

给定上述两表，求，以商品为单位汇总出各自的销量

-- 对于大数据而言，预计算还是有好处的
SELECT t1.item_no,
       t2.total_qty
  FROM items t1
  LEFT JOIN(
        SELECT item_no,
               SUM(quantity) AS total_qty
          FROM salehistory
         GROUP BY item_no
       ) t2
    ON t1.item_no=t2.item_no;
    -- 1对多 JOIN 行数并不会增加
    SELECT I.item_no,
       SUM(SH.quantity) AS total_qty
  FROM Items I
  LEFT OUTER JOIN SalesHistory SH
    ON I.item_no = SH.

04 JOIN运算

给定两个集合 A,B

1. FULL OUTER JOIN 等于 A union B，结果包含 1，2，3。
2. INNER JOIN 相当于 3
3. A 和 B的异或集是 1 和 2

 -- 求异或集
SELECT COALESCE(t1.id,t2.id) AS id,
       COALESCE(t1.name,t2.name) AS name
  FROM A t1
   FULL OUT JOIN B t2
    ON t1.id=t2.id
 WHERE t2.id IS NULL
    OR t1.id IS NULL

5. LEFT SEMI JOIN则是IN的高效写法，起到过滤的作用。

SELECT *
FROM tbl_A t1
LEFT SEMI JOIN tlb_B t2
ON t1.id=t2.id

--等价于
SELECT *
FROM tbl_A t1
WHERE t1.id
IN(SELECT id FROM tlb_B t2)