片方のリストに有って、他方に無いものを探すSQL

システム開発後のデータ整合性調査や不具合調査などの中で、二つのテーブルにあるIDなどを比較、片方にしかないもの（いわゆる差異、差分）を探すという場面があったりします。こういう時、意外にみんな必死にExcelに貼り付けて比較式を書いたり、Accessに取り込んで……とかやってるんですけど、実は簡単に結果は出せます。

片方の結果にあって、もう一方に無いものを探すSQL

集合演算でいう差集合を出す命令、実はSQLにすでに用意されています。「except」です。逆に両方の集合にあるものすべてを取る和集合は「union」といえばピンとくるでしょうか。こちらは結構使う場面もあるかと思いますが、exceptは知らない人が多いですね。

使い方は以下のような感じ。

select UserNo from UserTable
except
select UserNo from AttendantTable

上記の例だと、UserTableにあって、AttendantTableに無いUserNoのリストが出てきます。

片方がデータベースに無いリストの場合

では、上記の例で片方がデータベースのテーブルではなく、お客さんからもらったExcelに列挙されたユーザー番号リストだった場合はどうしましょうか。

こういう場合、SQL Serverだとこんな感じでExcelの内容を強制的にテーブルのように仕立てあげて、exceptで比較すればいけます。

select UserNo from UserTable
except
select * from (
    select '400384' as UserNo union all 
    select '402732' as UserNo union all 
    select '404459' as UserNo union all 
    select '410006' as UserNo union all 
    select '410556' as UserNo union all 
        ：
    select '410508' as UserNo union all 
    select '410500' as UserNo
) as GivenList

上記の例だと、UserTableにあって、Union all でくっつけて作ったリスト（一時的にGivenListと名付けました）に無いUserNoのリストが得られます。

このUnion allの部分は、Excelの一覧に1列追加して、追加した列にselect文を組み立てる文字列結合式を設定すれば簡単に作れますよね。

とりあえず忘れないようにメモ。