MySQLでテーブルに存在しない値だけを抽出する方法（UNION ALLとNOT EXISTSの活用）

MySQLでテーブルに存在しない値を照会するには？

MySQLで「テーブルにまだ登録されていない値だけ」を取得したい場合、UNION ALLとWHERE NOT EXISTSを組み合わせるのが有効です。サブクエリで候補となる値をUNION ALLで列挙し、NOT EXISTSによって既にテーブル内に存在する値を除外することで、存在しない値のみを抽出できます。

ここでは、実際の手順をサンプルコードとともに解説します。

1. サンプルテーブルを作成する

まず、自動採番（AUTO_INCREMENT）のカラムを持つテーブルを作成します。

mysql> create table DemoTable1918
    (
    Value int NOT NULL AUTO_INCREMENT PRIMARY KEY
    );
Query OK, 0 rows affected (0.00 sec)

2. テーブルにレコードを挿入する

INSERT文を使って、複数のレコードを登録します。AUTO_INCREMENTにより、値は自動的に1から採番されます。

mysql> insert into DemoTable1918 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1918 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1918 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1918 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1918 values();
Query OK, 1 row affected (0.00 sec)

3. テーブルの内容を確認する

SELECT文ですべてのレコードを表示してみましょう。

mysql> select * from DemoTable1918;

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

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

この時点で、テーブルには「1」から「5」までの値が登録されています。

4. テーブルに存在しない値を照会する

次に、候補となる値（6、7、8）をUNION ALLで列挙したサブクエリを作成し、NOT EXISTSでテーブル内に既に存在する値を除外します。

mysql> select tbl.Value
    from ( select 6 as Value union all select 7 union all select 8 ) tbl
    where not exists ( select 1 from DemoTable1918 tbl1
    where tbl1.Value=tbl.Value);

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

+-------+
| Value |
+-------+
|     6 |
|     7 |
|     8 |
+-------+
3 rows in set (0.00 sec)

ポイント解説

このクエリの仕組みは以下のとおりです。

• FROM句のサブクエリ： 「select 6 as Value union all select 7 union all select 8」により、チェック対象の値（6、7、8）を仮想的なテーブルとして生成します。
• WHERE NOT EXISTS： 各候補の値がDemoTable1918に存在するかどうかを確認し、存在しない行だけを結果として返します。

なお、NOT IN句を使った方法もありますが、対象カラムにNULLが含まれる場合に意図しない結果になることがあるため、NOT EXISTSの方が安全でパフォーマンスにも優れています。大量のデータを扱う場合も、適切なインデックスが設定されていればNOT EXISTSは効率的に動作します。

この手法は、マスタデータへの未登録値のチェックや、データ整合性の検証など、さまざまな場面で活用できます。