たまに必要に駆られるのが、２つのテーブルを比較して、１つのテーブルには存在するが、もう一方のテーブルにはないレコードを抽出するというもの。
いくつか方法がありますが、今回は主な２つをご紹介。特に②の方法が汎用的で、原理を覚えておくと、様々な場面で応用がききます。
①差集合演算を使う・・・簡単ですが、比較するテーブルの列数、型を合わせる必要あり。
②SQLのみで抽出・・・汎用的に使えます。一度原理を覚えると応用がききます。
はじめにデータを用意
では、さっそくやってみます。比較するテーブルは、この記事で用意した【売上テーブル】と【顧客テーブル】を使うことにしましょう。
各々のテーブルには以下のようにデータが入っていることにします。
①差集合演算を使う
各DBには演算のための組み込み関数があり、差分を演算するものもあります。
PostgreSQL、Oracle、DB2・・・minus
MySQL、SQLServer・・・except
上記は、あるSQLの結果セットから、他方のSQLの結果セットを「引き算」した結果を返すというものです。つまり、
select 顧客CD from 売上
minus
select 顧客CD from 顧客というSQLをOracleで発行すると、
ということになり、「C003,(NULL),TMP」が戻ります。
この演算子を使うときに気をつけるべきなのは、「両方のテーブルからのselect結果の列数、型が同じでなければならない」とうことです。
単純に引き算するのですから、同じでなければ比較ができないということになっているのですね。
②SQLのみで抽出
両方が同じ出なければいけないなんて、実際に使う場面はそう多くないかもしれません。現実的には、両方のテーブルで同じ項目はいくつか持っているものの、異なる項目も多い中で、比較することが大半なのではないかと思います。
そんなときには、次の原理を覚えておくと便利です。
前回の記事で、「OUTER JOIN」の原理を説明しました。これを使うと、「片一方の結合元のテーブルはそのままに、もう一つのテーブルを、結合条件で結合していく」のでしたね。結合する方のテーブルに値がない場合は、NULLとなりました。
つまり、
select * from 売上
left outer join 顧客 on (売上.顧客CD = 顧客.顧客CD)というSQLを発行すると、
という結果が返ります。ここで、上記の表の赤い太文字の「NULL」のある行(レコード)に注目してみましょう。このレコードはまさに、「複数のテーブルを(顧客CDで)比較して、他方にないレコードを探す」という目的に合った結果であることが分かります。
これを、SQLに直すと、
select * from 売上 left outer join 顧客 on (売上.顧客CD = 顧客.顧客CD)
where 顧客.顧客CD is nullということになりますね。これが原理です。２つのテーブルを外部結合でくっつけて、もう一方のテーブルにない行を抽出すると覚えておくと、いろいろな場面で役立ちますよ！