Zenn
Open4

【達人に学ぶSQL徹底指南書メモ】9. SQLで集合演算

SQL
DB
churuchuruchuruchuru

9.1 テーブル同士のコンペア - 集合の相等性のチェック

DB の環境を移行したり、バックアップと最新環境を比較したい場合など、2 つのテーブルが等しいか否かを調べたい場合がある。

9.1.1 S UNION S = S を利用する

2 つのテーブルの行数が等しいことを確認済みだと仮定する。
以下のクエリの結果が行数と等しければ、テーブル同士が相当であることがわかる。

SELECT
  COUNT(*) AS row_count
FROM (
  SELECT * FROM tbl_A
  UNION
  SELECT * FROM tbl_B
) AS TMP;
メリット
  • 列名や列数は一切知る必要がない
  • NULL を含むテーブルにも使える
デメリット
  • 行数を調べる事前準備が必要
集合演算子（UNION, INTERSECT）における ALL オプションの有無による違い
  • ALL オプションを付けなければ重複行を排除する（暗黙のソートが発生）
  • ALL オプションを付けるとソートが行われないためパフォーマンスが向上する

9.1.2 (A INTERSECT B) ⊆ (A UNION B) を利用する

(A UNION B) EXCEPT (A INTERSECT B) が空集合かどうかを判定すればよい。

SELECT
  CASE
    WHEN COUNT(*) = 0 THEN '等しい'
    ELSE '異なる'
  END AS result
FROM (
  (
    SELECT * FROM tbl_A
    UNION
    SELECT * FROM tbl_B
  )
  EXCEPT
  (
    SELECT * FROM tbl_A
    INTERSECT
    SELECT * FROM tbl_B
  )
) AS TMP;
メリット
  • 列名や列数は一切知る必要がない
  • NULL を含むテーブルにも使える
  • 行数を調べる事前準備が不要
デメリット
  • 集合演算 3 回分のソートが発生するため、パフォーマンスが落ちる
    • 頻繁に実行するクエリではないため許容範囲
  • INTERSECT, EXCEPT をサポートしていない MySQL では使えない
churuchuruchuruchuru

9.2 差集合で関係除算を表現する

Skills

skill
Oracle
UNIX
Java

EmpSkills

emp skill
相田 Oracle
相田 UNIX
相田 Java
相田 C++
神崎 Oracle
神崎 UNIX
神崎 Java
平井 UNIX
平井 Oracle
平井 PHP
平井 Perl
平井 C++
若田部 Perl
渡来 Oracle

EmpSkills テーブルから Skills テーブルの技術すべてに精通した社員を探す。

9.2.1 剰余を持った除算

SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE
  NOT EXISTS(
    SELECT skill FROM Skills
    EXCEPT
    SELECT skill FROM EmpSkills ES2 WHERE ES1.emp = ES2.emp
  );

結果

emp
相田
神崎

9.2.2 厳密な関係除算

SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE
  NOT EXISTS(
    SELECT skill FROM Skills
    EXCEPT
    SELECT skill FROM EmpSkills ES2 WHERE ES1.emp = ES2.emp
  )
  AND
  NOT EXISTS(
    SELECT skill FROM EmpSkills ES3 WHERE ES1.emp = ES3.emp
    EXCEPT
    SELECT skill FROM Skills
  );

結果

emp
神崎

「(A ⊆ B) かつ (A ⊇ B) ⇔ (A = B)」を利用している。

churuchuruchuruchuru

9.3 等しい部分集合を見つける（除算の一般化）

SupParts

sup part
A ボルト
A ナット
A パイプ
B ボルト
B パイプ
C ボルト
C ナット
C パイプ
D ボルト
D パイプ
E ヒューズ
E ナット
E パイプ
F ヒューズ

数も種類もまったく同じ部品を取り扱う供給業者のペアを選択する。

SELECT
  SP1.sup AS s1,
  SP2.sup AS s2
FROM
  SupParts SP1
  INNER JOIN SupParts SP2
    ON SP1.sup < SP2.sup  -- 業者の組み合わせを作る
    AND SP1.part = SP2.part  -- 条件1. 同じ種類の部品を扱う
GROUP BY
  SP1.sup, SP2.sup
HAVING  -- 条件2. 同数の部品を扱う
  COUNT(*) = (
    SELECT COUNT(*) FROM SupParts SP3 WHERE SP3.sup = SP1.sup
  )
  AND
  COUNT(*) = (
    SELECT COUNT(*) FROM SupParts SP4 WHERE SP4.sup = SP2.sup
  );

結果

s1 s2
A C
B D
churuchuruchuruchuru

9.4 重複行を削除する高速なクエリ

Products

name price
りんご 50
みかん 100
みかん 100
みかん 100
バナナ 80

結果

name price
りんご 50
みかん 100
バナナ 80
相関サブクエリの利用（高速でない）
DELETE FROM Products
WHERE
  rowid < (
    SELECT MAX(P2.rowid)
    FROM Products P2
    WHERE
      Products.name = P2.name
      AND Products.price = P2.price
  );
9.4.1 補集合を EXCEPT で求める
DELETE FROM Products
WHERE rowid IN (
  SELECT rowid  -- 全体のrowid
  FROM Products
  EXCEPT
  SELECT MAX(rowid)  -- 残すべきrowid
  FROM Products
  GROUP BY name, price
);
9.4.2 補集合を NOT IN で求める
DELETE FROM Products
WHERE rowid NOT IN (
  SELECT MAX(rowid)
  FROM Products
  GROUP BY name, price
);