SQLで差分を取得・抽出する2つの方法
SQLを操作する際に、2つのテーブルを比較する機会があると思います。
「あるテーブルには存在するデータだが、もう片方のテーブルには存在しないデータを取得したい」
このような、テーブル間での「差分」データを取得する方法についてこの記事では解説します。
方法はいくつかあるのですが、ここでは以下の2つの方法をご紹介します。
| 方法 | 特徴 |
|---|---|
| 差集合演算を使用 | 簡単に使えるが比較するテーブルのカラム数とデータ型を合わせる必要がある |
| SQLだけを使用 | 差分を取得する原理をしると、汎用的に使え応用が利く |
差分の取得・抽出を理解するために、実際に操作して確認してみましょう。
ここでは、次の【販売テーブル】と【ユーザーテーブル】を用意して確認していきます。
それぞれのテーブルには、次の表のデータ・レコードを格納しました。
【販売テーブル】
| sale_id | sale_date | sale_fee | user_id |
|---|---|---|---|
| 1 | 2020-08-01 | 5300 | U101 |
| 2 | 2020-08-16 | 6000 | U102 |
| 3 | 2020-09-07 | 8200 | U103 |
| 4 | 2020-09-22 | 6500 | |
| 5 | 2020-10-02 | 10200 | U101 |
| 6 | 2020-10-18 | 8500 | U400 |
【ユーザーテーブル】
| user_id | user_name | user_address |
|---|---|---|
| U101 | Satou | Tokyo |
| U102 | Ishida | Osaka |
| U103 | Ueki | Tokyo |
次の章で、差集合演算を使った取得・抽出方法を見ていきましょう！
【差集合演算】を使用して差分を取得・抽出
まずは、差集合演算を使用して差分を取得・抽出してみます。
各DBMSには、差分を演算するための組み込み関数が用意されているのです。
それぞれ、次の演算子になります。
| DBMS名 | 演算子・関数 |
|---|---|
| MySQL | 対応なし |
| PostgreSQL | EXCEPT演算子 |
| Oracle | MINUS演算子 |
| SQL Server | EXCEPT演算子 |
| Access | 対応なし |
なお、MySQLにはMINUS演算子・EXCEPT演算子の対応がないため、後述するSQLだけを使用した差分の取得・抽出が必要です。
MINUS演算子・EXCEPT演算子は、あるSQLの結果セットから、もう片方のSQLの結果セットを引き算（差）した結果を返します。
MINUS演算子・EXCEPT演算子の記述方法は、次のようになります。
--MINUS演算子 SELECT カラム名1, カラム名2, … FROM テーブル名1 MINUS SELECT カラム名1, カラム名2, … FROM テーブル名2 --EXCEPT演算子 SELECT カラム名1, カラム名2, … FROM テーブル名1 EXCEPT SELECT カラム名1, カラム名2, … FROM テーブル名2
上記を見ての通り、記述方法は同じで使う演算子だけが異なります。
では実際に、先ほど用意したテーブルを使って確認してみましょう。
それぞれのテーブルのデータは次のように用意されています。
mydb=# SELECT * FROM sale_list;
sale_id | sale_date | sale_fee | user_id
---------+------------+----------+---------
1 | 2020-08-01 | 5300 | U101
2 | 2020-08-16 | 6000 | U102
3 | 2020-09-07 | 8200 | U103
4 | 2020-09-22 | 6500 |
5 | 2020-10-02 | 10200 | U101
6 | 2020-10-18 | 8500 | U400
(6 rows)
mydb=# SELECT * FROM user_list;
user_id | user_name | user_address
---------+-----------+--------------
U101 | Satou | Tokyo
U102 | Ishida | Osaka
U103 | Ueki | Tokyo
(3 rows)
ここでは、【販売テーブル】と【ユーザーテーブル】から user_idカラム の差集合を取得・抽出します。
記述方法は次の通りです。
SELECT user_id FROM sale_list EXCEPT SELECT user_id FROM user_list;
上記のSQL文を実行すると、次のデータを取得できました。
mydb=# SELECT user_id FROM sale_list EXCEPT SELECT user_id FROM user_list; user_id --------- U400 (2 rows)
user_listテーブルに登録されていない NULL値 と U400 というデータが取得・抽出できています。
このように、差集合演算を使うとカンタンに目的のデータを取得・抽出可能です。
【SQLだけ】を使用して差分を取得・抽出
では次に、SQLだけを使用して差分を取得・抽出する方法をみていきましょう。
SQL操作でテーブル間の差を求める場合には、それぞれ異なった項目を持ったテーブルを比較することが多いはずです。
差集合演算はカンタンに使えて便利ですが、比較するテーブルの項目が全く同じでなければ、一部のデータ・項目しか取得・抽出できません。
そのため、場合によっては扱いにくいと感じてしまうのです。
一方で、SQLだけを使用して差分を取得・抽出を覚えておくと、どんなテーブルでも扱いやすくなるメリットがあります。
そのやり方は、「OUTER JOIN句」を使う方法です。
OUTER JOIN句は「外部結合」のことで、一致しないデータも含めて2つのテーブルを結合させ、テーブル間のデータをひとまとめにした情報を取得します。
OUTER JOIN句については、以下の記事で詳しく解説しているので参考までに。
【関連記事】
▶︎【SQL】外部結合でテーブルを結合させる。OUTER JOIN句について詳しく解説。
では実際に、OUTER JOIN句を使って2つのテーブルを外部結合してみます。
記述方法は次の通りです。
SELECT * FROM sale_list LEFT OUTER JOIN user_list ON (sale_list.user_id = user_list.user_id);
それぞれのテーブルの user_idカラム をもとに、sale_listテーブル に user_listテーブル を結合しています。
上記のSQL文を実行すると、次のような結果が取得できます。
mydb=# SELECT * FROM sale_list LEFT OUTER JOIN user_list ON (sale_list.user_id = user_list.user_id);
sale_id | sale_date | sale_fee | user_id | user_id | user_name | user_address
---------+------------+----------+---------+---------+-----------+--------------
1 | 2020-08-01 | 5300 | U101 | U101 | Satou | Tokyo
2 | 2020-08-16 | 6000 | U102 | U102 | Ishida | Osaka
3 | 2020-09-07 | 8200 | U103 | U103 | Ueki | Tokyo
4 | 2020-09-22 | 6500 | | | |
5 | 2020-10-02 | 10200 | U101 | U101 | Satou | Tokyo
6 | 2020-10-18 | 8500 | U400 | | |
(6 rows)
この結合したテーブルには NULL になっているデータがありますよね。
このデータこそ、それぞれのテーブルの「差分」となっているのです。
この NULL を条件にしてデータを取得すると、差分の取得ができそうです。
先ほどのSQL文に条件を付け足したものが、以下のSQL文になります。
SELECT * FROM sale_list LEFT OUTER JOIN user_list ON (sale_list.user_id = user_list.user_id) WHERE user_list.user_id IS NULL;
「WHERE user_list.user_id IS NULL」を追加しました。
これで、user_listテーブル の user_idカラム が NULL（登録なし） のデータを取得できます。
mydb=# SELECT * FROM sale_list LEFT OUTER JOIN user_list ON (sale_list.user_id = user_list.user_id) WHERE user_list.user_id IS NULL;
sale_id | sale_date | sale_fee | user_id | user_id | user_name | user_address
---------+------------+----------+---------+---------+-----------+--------------
4 | 2020-09-22 | 6500 | | | |
6 | 2020-10-18 | 8500 | U400 | | |
(2 rows)
上記のように、期待した通りの結果になっているはずです。
