SQLで、複数テーブルから他方に無い(存在しない)レコードを抽出する

たまに必要に駆られるのが、2つのテーブルを比較して、1つのテーブルには存在するが、もう一方のテーブルにはないレコードを抽出するというもの。

いくつか方法がありますが、今回は主な2つをご紹介。

①差集合演算を使う・・・簡単ですが、比較するテーブルの列数、型を合わせる必要あり。

②SQLのみで抽出・・・汎用的に使えます。一度原理を覚えると応用が利きます。

 

では、さっそくやってみます。比較するテーブルは、この記事で用意した【売上テーブル】と【顧客テーブル】を使うことにしましょう。

各々のテーブルには以下のようにデータが入っていることにします。

【売上テーブル】

売上ROW_ID 売上日 顧客CD
1 13-01-01 C001
2 13-02-01 C002
3 13-03-01 C003
4 13-04-01
5 13-05-01 TMP
6 13-06-01 C001

 

【顧客テーブル】

顧客CD 顧客名
C001 田中さん
C002 鈴木さん

 

①差集合演算を使う

各DBには演算のための組み込み関数があり、差分を演算するものもあります。

PostgreSQL、Oracle、DB2・・・minus

MySQL、SQLServer・・・except

上記は、あるSQLの結果セットから、他方のSQLの結果セットを「引き算」した結果を返すというものです。つまり、

select 顧客CD from 売上
minus
select 顧客CD from 顧客

 

というSQLをOracleで発行すると、

顧客CD(売上) minus(except) 顧客CD(顧客)
C001              ー C001
C002              ー C002
C003
(NULL)
TMP

 

ということになり、「C003,(NULL),TMP」が戻ります。この演算子を使うときに気をつけるべきなのは、「両方のテーブルからのselect結果の列数、型が同じでなければならない」とうことです。単純に引き算するのですから、同じでなければ比較ができないということになっているのですね。

 

②SQLのみで抽出

両方が同じ出なければいけないなんて、実際に使う場面はそう多くないかもしれません。現実的には、両方のテーブルで同じ項目はいくつか持っているものの、異なる項目も多い中で、比較することが大半なのではないかと思います。

そんなときには、次の原理を覚えておくと便利です。

前回の記事で、「OUTER JOIN」の原理を説明しました。これを使うと、「片一方の結合元のテーブルはそのままに、もう一つのテーブルを、結合条件で結合していく」のでしたね。結合する方のテーブルに値がない場合は、NULLとなりました。

つまり、

select * from 売上 left outer join 顧客 on (売上.顧客CD = 顧客.顧客CD)

 

というSQLを発行すると、

売上ROW_ID 売上日 顧客CD 顧客CD 顧客名
1 13-01-01 C001 C001 田中さん
2 13-02-01 C002 C002 鈴木さん
3 13-03-01 C003 NULL NULL
4 13-04-01 NULL NULL NULL
5 13-05-01 TMP NULL NULL
6 13-06-01 C001 C001 田中さん

 

という結果が返ります。ここで、上記の表の赤い太文字の「NULL」のある行(レコード)に注目してみましょう。このレコードはまさに、「複数のテーブルを(顧客CDで)比較して、他方にないレコードを探す」という目的に合った結果であることが分かります。

これを、SQLに直すと、

select * from 売上 left outer join 顧客 on (売上.顧客CD = 顧客.顧客CD)
where 顧客.顧客CD is null

 

ということになりますね。これが原理です。2つのテーブルを外部結合でくっつけて、もう一方のテーブルにない行を抽出すると覚えておくと、いろいろな場面で役立ちますよ!

 



こんな記事も関係あるかも。読んでみてね。

One Response to “SQLで、複数テーブルから他方に無い(存在しない)レコードを抽出する”

  1. とても参考になりました。
    NULL指定すればいいとは検討つきませんでした。
    今後も是非よらせていただきます。

コメントを残す

サブコンテンツ

このページの先頭へ