SQL修行vol.9

2024年3月27日 10:50

『達人に学ぶSQL徹底指南書』ー8.外部結合の使い方

大雨が止んだところで外に出たい気持ちを抑えつつ、今日の学びのまとめをば。今日はこの本を読む前に結合について、YouTubeとChatGTPを使って一通り復習した後でスタート。

活用ケース１：行列変換：その１（行→列）

例：社員が受講した研修コースのクロス表を作成する

--外部結合の利用
SELECT
   C0.name,
   CASE WHEN (C1.name IS NOT NULL THEN '○' ELSE NULL END) AS "SQL入門",
   CASE WHEN (C2.name IS NOT NULL THEN '○' ELSE NULL END) AS "UNIX基礎",
   CASE WHEN (C2.name IS NOT NULL THEN '○' ELSE NULL END) AS "Java中級"
FROM
   (SELECT DISTINCT name FROM Courses) C0
   LEFT OUTER JOIN
      (SELECT name FROM Courses WHERE course = 'SQL入門') C1
      ON C0.name = C1.name
   LEFT OUETR JOIN
      (SELECT name FROM Courses WHERE course = 'UNIX基礎') C2
      ON C0.name = C2.name
   LEFT OUETR JOIN
      (SELECT name FROM Courses WHERE course = 'Java中級') C3
      ON C0.name = C3.name;

--スカラサブクエリの利用
SELECT
   C0.name
   (SELECT '○'
    FROM Courses C1
    WHERE
       course = 'SQL入門'
       AND
      C1.name = C0.name) AS  "SQL入門",
   (SELECT '○'
    FROM Courses C2
    WHERE
       course = 'SQL入門'
       AND
      C2.name = C0.name) AS  "UNIX基礎",
 (SELECT '○'
    FROM Courses C3
    WHERE
       course = 'SQL入門'
       AND
      C3.name = C0.name) AS  "Java中級"
FROM
   (SELECT DISTINCT name FROM Courses) C0;

--CASE式を入れ子にする
SELECT
   name,
   CASE WEHN SUM(CASE WHEN course = 'SQL入門' THEN 1 ELSE 0 END) = 1
        THEN '○' ELSE NULL END AS "SQL入門",
   CASE WEHN SUM(CASE WHEN course = 'UNIX基礎' THEN 1 ELSE 0 END) = 1
        THEN '○' ELSE NULL END AS "UNIX基礎",
   CASE WEHN SUM(CASE WHEN course = 'Java中級' THEN 1 ELSE 0 END) = 1
        THEN '○' ELSE NULL END AS "Java中級"
FROM
   Courses
GROUP BY
   name;

🐣スカラサブクエリ
取得結果が単一の値になるサブクエリのこと。
SELECT句で使うと、SELECTに返された1行ずつについて実行されるので、高コストになる。

活用ケース２：行列変換：その２（列→行）

例：行持ちの形式に変換する

SELECT employee, child1 AS child FROM Personnel
UNION ALL
SELECT employee, child2 AS child FROM Personnel
UNION ALL
SELECT employee, child3 AS child FROM Personnel;

例：社員一覧と子ども一覧から社員の子どもリストを作成する（子どもがいない社員も抽出する）

SELECT
   EMP.employee, Children.child
FROM
   Personnel EMP
      LEFT OUTER JOIN Children
      ON Children.child IN (EMP.child1, EMP.child2, EMP.child3);

活用ケース３：クロス表で入れ子の表側を作る

例：年齢階級マスタ、性別マスタ、人口のテーブルを1つのテーブル（年齢階級・性別毎）にする

--集約からの結合
SELECT
   MASTER.age_class,
   MASTER.sex_cd,
   DATA.pop_tohoku,
   DATA.pop_kanto
FROM
   (TblAge CROSS JOIN TblSex) MASTER
   LEFT OUTER JOIN
      (SELECT
          age_class,
          sex_cd,
          SUM(CASE WHEN DATA.pref_name = IN ('秋田', '青森') THEN population ELSE 0 END) AS pop_tohoku,
          SUM(CASE WHEN DATA.pref_name = IN ('東京', '千葉') THEN population ELSE 0 END) AS pop_kanto
       FROM TblPop
       GROUP BY
          age_class, sex_cd
       ) DATA   
   ON MASTER.age_class = DATA.age_class
      AND
      MASETER.sex_cd = DATA.sex_cd;

--中間テーブルを減らしたパターン
SELECT
   MASTER.age_class,
   MASTER.sex_cd,
   SUM(CASE WHEN DATA.pref_name = IN ('秋田', '青森') THEN population ELSE 0 END) AS pop_tohoku,
   SUM(CASE WHEN DATA.pref_name = IN ('東京', '千葉') THEN population ELSE 0 END) AS pop_kanto
FROM
   (SELECT age_class, sex_cd
    FROM TblAge CROSS JOIN TblSex MASTER)
   LEFT OUTER JOIN PopTbl DATA
      ON MASTER.age_class = DATA.age_class
      AND MASTER.sex_cd = DATA.sex_cd
GROUP BY
   MASTER.age_class, MASTER.sex_cd;

活用ケース４：掛け算としての結合

例：商品マスタと商品の売上履歴から商品ごとに総計でいくつ売れたかを調べる

--一対多の結合をしてから集約する
SELECT
   I.itemno,
   SUM(SH.quantitu) AS total_qty
FROM
   Items I
   LEFT OUTER JOIN SalesHistory SH
   ON I.item_no = SH.item_no
GROUP BY
   I.item_no;

--集約してから一対一の結合を行う
SELECT
   I.item_no,
   SH.total_qty
FROM
   (SELECT item_no FROM Item I)
   LEFT OUTER JOIN
      (SELECT item_no,
              SUM(quantity) AS total_qty
       FROM SalesHistory SH
       GROUP BY item_no)
   ON I.item_no = SH.item_no;

結合は集合演算

内部結合　　　　　　　：積集合（INTERSECT）
完全外部結合　　　　　：和集合（UNION）
右外部結合・左外部結合：差集合

🐣差集合A-Bを求める

SELECT
   A.id, A.name AS A_name
FROM
   Class_A A
   LEFT OUTER JOIN Class_B B
   ON Class_A.id = Class_B.id
WHERE
   B.name IS NULL;

🐣差集合B-Aを求める

SELECT
   A.id, A.name AS A_name
FROM
   Class_A A
   ROGHT OUTER JOIN Class_B B
   ON Class_A.id = Class_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 IN NULL
   OR
   B.name IS NULL;