MySQLで2つのテーブルを比較して欠落しているIDを抽出する方法

MySQLで2つのテーブルを比較し、片方のテーブルに存在しないID（欠落しているID）を取得したい場合は、サブクエリとNOT IN句を組み合わせるのが基本的な方法です。

基本構文

以下が、2つのテーブルを比較して欠落しているIDを返すための基本構文です。

SELECT テーブル名1.IDカラム名 FROM テーブル名1
WHERE IDカラム名 NOT IN (SELECT テーブル名2.IDカラム名 FROM テーブル名2);

この構文では、サブクエリで2つ目のテーブルに存在するIDの一覧を取得し、1つ目のテーブルの中でその一覧に含まれないIDだけを抽出しています。

それでは、実際にサンプルデータを使って動作を確認してみましょう。

ステップ1：1つ目のテーブル（First_Table）を作成する

まず、比較元となるテーブルを作成します。

mysql> create table First_Table
    -> (
    -> Id int
    -> );
Query OK, 0 rows affected (0.88 sec)

次に、INSERT文でレコードを挿入します。

mysql> insert into First_Table values(1);
Query OK, 1 row affected (0.68 sec)
mysql> insert into First_Table values(2);
Query OK, 1 row affected (0.29 sec)
mysql> insert into First_Table values(3);
Query OK, 1 row affected (0.20 sec)
mysql> insert into First_Table values(4);
Query OK, 1 row affected (0.20 sec)

SELECT文ですべてのレコードを確認しましょう。

mysql> select *from First_Table;

実行結果は以下の通りです。

+------+
| Id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

First_Tableには「1」「2」「3」「4」の4件のIDが登録されました。

ステップ2：2つ目のテーブル（Second_Table）を作成する

続いて、比較先となるテーブルを作成します。

mysql> create table Second_Table
    -> (
    -> Id int
    -> );
Query OK, 0 rows affected (0.60 sec)

こちらにもINSERT文でレコードを挿入します。

mysql> insert into Second_Table values(2);
Query OK, 1 row affected (0.19 sec)
mysql> insert into Second_Table values(4);
Query OK, 1 row affected (0.20 sec)

登録内容を確認しておきましょう。

mysql> select *from Second_Table;

実行結果は以下の通りです。

+------+
| Id   |
+------+
|    2 |
|    4 |
+------+
2 rows in set (0.00 sec)

Second_Tableには「2」と「4」の2件のみ登録されています。つまり、First_Tableにある「1」と「3」がSecond_Tableには存在しないIDということになります。

ステップ3：2つのテーブルを比較して欠落しているIDを取得する

いよいよ、NOT IN句とサブクエリを使って比較クエリを実行します。

mysql> select First_Table.Id from First_Table where
    -> First_Table.Id NOT IN(select Second_Table.Id from Second_Table);

実行結果は以下の通りです。

+------+
| Id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)

期待どおり、First_Tableには存在するもののSecond_Tableには存在しないID「1」と「3」が返されました。

補足：パフォーマンスを考慮した代替手段

NOT IN句は直感的でわかりやすい一方、対象カラムにNULL値が含まれる場合や、レコード数が非常に多い場合は意図しない結果やパフォーマンス低下につながることがあります。そのようなケースでは、LEFT JOINやNOT EXISTSを使う方法も検討するとよいでしょう。

-- LEFT JOIN を使った例
SELECT f.Id FROM First_Table f
LEFT JOIN Second_Table s ON f.Id = s.Id
WHERE s.Id IS NULL;

-- NOT EXISTS を使った例
SELECT f.Id FROM First_Table f
WHERE NOT EXISTS (
  SELECT 1 FROM Second_Table s WHERE s.Id = f.Id
);

どちらの方法でも同じ結果が得られますので、データ量やNULLの扱い方に応じて最適な方法を選択してください。