Zenn
Open4

【達人に学ぶSQL徹底指南書メモ】8. 外部結合の使い方

SQL
DB
churuchuruchuruchuru

8.1 完全外部結合

標準 SQL では、以下の 3 種類の外部結合の構文が定義されている。

  • 左外部結合（LEFT OUTER JOIN）
  • 右外部結合（RIGHT OUTER JOIN）
  • 完全外部結語（FULL OUTER JOIN）

Class_A

id name
1 田中
2 鈴木
3 伊集院

Class_B

id name
1 田中
2 鈴木
4 西園寺 
SELECT
  COALESCE(A.id, B.id) AS id,
  A.name AS A_name,
  B.name AS B_name
FROM
  Class_A A
  FULL OUTER JOIN Class_B B
    ON A.id = B.id;

結果

id A_name B_name
1 田中 田中
2 鈴木 鈴木
3 伊集院
4 西園寺

完全外部結合が使えない環境での代替方法は以下。
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;

結合は集合演算と言える。

  • 内部結合 = 積集合（INTERSECT）
  • 完全外部結合 = 和集合（UNION）
churuchuruchuruchuru

8.2 外部結合で集合演算

8.2.1 外部結合で差集合を求める（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;

結果

id A_name
3 伊集院

8.2.2 外部結合で差集合を求める（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;

結果

id B_name
4 西園寺

8.2.3 完全外部結合で排他的和集合を求める

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;

結果

id name
3 伊集院
4 西園寺
churuchuruchuruchuru

8.3 掛け算としての結合

Items

item_no item
10 SD カード
20 CD-R
30 USB メモリ
40 DVD

SalesHistory

sale_date item_no quantity
2018-10-01 10 4
2018-10-01 20 10
2018-10-01 30 3
2018-10-03 10 32
2018-10-03 30 12
2018-10-04 20 22
2018-10-04 30 7

結果

item_no total_qty
10 36
20 32
30 22
40

結合の前に集約することで、一対一の関係を作る

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;

中間ビュー SH を作り、主キーによる一対一の結合をしている。

SH

item_no total_qty
10 36
20 32
30 22

以下の理由からパフォーマンスの観点から問題が残る。

  • SH のデータを一度メモリ上に保持しなければならない
  • SH には主キーのインデックスが存在しないため、結合条件で使用することができない

集約の前に一対多の結合を行う

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;

結合は、一対一でなくとも一対多ならば行数は増えない。

ただし、Items テーブルの item_no に重複が存在するようなケースでは、多対多の結合になってしまうため、この方法は使えない。
その場合は、どちらかを集約して一対多の関係にもっていく必要がある。

churuchuruchuruchuru

8.4 クロス表で入れ子の表側を作る

TblAge

age_class age_range
1 21~30 歳
2 31~40 歳
3 41~50 歳

TblSex

sex_cd sex
m 男
f 女

TblPop

pref_name age_class sex_cd population
秋田 1 m 400
秋田 3 m 1000
秋田 1 f 800
秋田 3 f 1000
青森 1 m 700
青森 1 f 500
青森 3 f 800
東京 1 m 900
東京 1 f 1500
東京 3 f 1200
千葉 1 m 900
千葉 1 f 1000
千葉 3 f 900

結果

age_class sex_cd pop_tohoku pop_kanto
1 m 1100 1800
1 f 1300 2500
2 m
2 f
3 m 1000
3 f 1800 2100

集約が先

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
  ) AS 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
    ) AS DATA
    ON MASTER.age_class = DATA.age_class
      AND MASTER.sex_cd = DATA.sex_cd;

パフォーマンスを考慮するならば、中間ビューを 2 つ作るのは無駄が多い。

結合が先

SELECT
  MASTER.age_class AS age_class,
  MASTER.sex_cd AS 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 (
    SELECT age_class, sex_cd
    FROM TblAge CROSS JOIN TblSex
  ) AS MASTER
  LEFT OUTER JOIN TblPop AS DATA
    ON MASTER.age_class = DATA.age_class
      AND MASTER.sex_cd = DATA.sex_cd
GROUP BY MASTER.age_class, MASTER.sex_cd;