JOINによる集合演算


SQLで集合演算といえば、INTERSECT,UNION,EXCEPT を使うものと思っていました。INTERSECTは図の②に対応、UNIONは図の①+②+③、EXCEPTは①または③を求めるのに使えますね。

ベン図

ベン図

一方、JOINは表の列を増やすためのもの、というとらえ方をしていたのです。例えばーINNER JOINで購買履歴と商品マスタをつなげ、購入日と商品名をリストアップする/あるいは、LEFT JOINで、顧客マスタと購買履歴をつなげて、未購買顧客も含めて顧客情報と購入額をリストアップするーような。

ところで、INTERSECTなどは、二つのSELECTの結果の列全ての組み合わせを比較します。そのため、ID項目を共有する二つの表AとBがあり、BにないIDを持つAのレコードを抽出するには以下のようなクエリとなります。

SELECT *
  FROM A
  JOIN (SELECT ID FROM A
        EXCEPT
        SELECT ID FROM B) C USING (ID);

Cでエイリアスしたサブクエリがちょっと長いなぁと思っていたのですが、ある時「JOIN」で書けるのでは?と思いつきました。

SELECT *
  FROM A
  LEFT JOIN B USING (ID)
 WHERE B.ID IS NULL;

LEFT JOINは図の①+②を残すものです。ここでB.ID IS NULLという条件を掛けると②が消えて①が残るという訳です。サブクエリが出ない分簡潔に書けますが、A EXCEPT Bという意図を読み取るのが慣れないと難しいかも知れません。

投稿前にこの書き方について解説しているサイトを探してみたところ、ミックさんのサイトがありました。またIDEA*IDEAで紹介されていた、Coding Horror: A Visual Explanation of SQL Joinsは網羅的に分かりやすくまとまっています。

なので、詳しくはこれらサイトを見ていただくとして、この発想に基づくクエリの例をいくつか紹介しておきます。

INSERT INTO "名簿"
SELECT *
  FROM "2013年住所変更者";

INSERT INTO "名簿"
SELECT "2012年住所変更者".*
  FROM "2012年住所変更者"
  LEFT JOIN "名簿" USING ("学籍番号")
 WHERE "名簿"."学籍番号" IS NULL;
...

重複したら先に登録したレコードを優先するという条件で複数の表をまとめようとしています。INSERT先のテーブルと結合するのがミソですね。

CREATE TABLE "訂正後" AS
SELECT coalesce("ID訂正表"."新ID", A.ID) AS ID
     , "元表"."項目1"
     , ...
  FROM "元表"
  LEFT JOIN "ID訂正表" ON "元表".ID = "ID訂正表"."旧ID";

集合演算とは少し違うかも知れません。訂正表には一部のIDのみ記載されている前提で、記載が無い場合に列の参照がNULLになることを利用した簡潔な表現になっています。UPDATEを使えばいいのでは?と思われるかも知れませんが、データマイニングの前処理においてはUPDATEDELETEを使わない方が都合が良いのです。またいつかその辺りについてまとめたいと思います。

SELECT count(A.ID) AS "Bでは残らずAでは残った件数"
     , count(B.ID) AS "Aでは残らずBでは残った件数"
     , count(*) AS "不一致件数"
  FROM "先月の選考結果" A
  FULL OUTER JOIN "今月選考し直した結果" B USING (ID)
 WHERE A.ID IS NULL
    OR B.ID IS NULL;

LEFT JOINではなく、FULL OUTER JOINを使うと、どちらかにしかないレコードを見つけることが出来ます。ある状況で二つの計算結果を一致させる必要があったのですが、どこに違いがあるか分からない状態からこのようなクエリを手がかりに調査を行ったことがあります。

JOINが集合演算を表していることに気づいてから、SELECT文を書くときには、FROM句から書き出す方が楽になった気がします。いきなり出来上がりの表をイメージするのではなく、どのような集合から情報を引き出そうとしているかをまず組み立てるべきですね。

今回は以上です。


This entry was posted in 技術. Bookmark the permalink.