特定のテーブルには存在するが、もう一方のテーブルには存在しないレコードを取得するSQL

データベースを操作する際、2つのテーブルを比較して、片方には存在するがもう片方には存在しないレコードを取得したいことがある。これは、データの不整合を検出したり、特定の差分を分析する際に非常に役立つ。

使用する条件

• 2つのテーブルには複数のカラムを含む複合主キーがあることを想定。
• これらのカラムを組み合わせて、一意のレコードを特定する必要がある。

基本的なクエリ構造

複合主キーを使用してテーブルを比較するには、LEFT JOINを使う方法が一般的だ。LEFT JOINを使用して、条件に一致しないレコードを抽出する。

SELECT a.*
FROM table1 a
LEFT JOIN table2 b
ON a.key1 = b.key1
   AND a.key2 = b.key2
   AND a.key3 = b.key3  -- 複合主キーの各カラムを結合条件に指定
WHERE b.key1 IS NULL;

クエリの説明

1. SELECT a.*: table1のすべてのカラムを選択する。
2. LEFT JOIN: table1のすべてのレコードを取得し、table2と結合する。ただし、結合条件に一致しないレコードも含める。
3. ON a.key1 = b.key1 AND a.key2 = b.key2 AND a.key3 = b.key3: 複合主キーの各カラムを使って、2つのテーブルを結合する。
4. WHERE b.key1 IS NULL: table2に存在しないレコードを抽出する。b.key1がNULLである場合は、table2に一致するレコードがないことを意味する。

実用例

例1: ユーザーデータの比較

2つのテーブルusers_2023とusers_2024があり、複合主キーがuser_idとemailだとする。

SELECT u23.*
FROM users_2023 u23
LEFT JOIN users_2024 u24
ON u23.user_id = u24.user_id
   AND u23.email = u24.email
WHERE u24.user_id IS NULL;

• 結果: users_2023に存在し、users_2024には存在しないレコードが取得される。

例2: 商品データの差分チェック

2つのテーブルproducts_oldとproducts_newがあり、複合主キーがproduct_idとcategory_idの場合。

SELECT po.*
FROM products_old po
LEFT JOIN products_new pn
ON po.product_id = pn.product_id
   AND po.category_id = pn.category_id
WHERE pn.product_id IS NULL;

• 結果: products_oldに存在し、products_newには存在しない商品データが取得される。

注意事項

• NULLチェック: LEFT JOINの後にWHERE b.key1 IS NULLとすることで、table2に存在しないレコードを絞り込む。結合条件に一致するレコードがない場合にNULLが返されるため、この条件を使用する。
• 複合主キー: 複合主キーをすべてON条件に含めることが重要。1つでも漏れると、正確な比較ができなくなる。