Oct 21, 2024 by Robert Gravelle

SQLの最も強力な機能の1つはJOIN操作であり、あるテーブルのすべての行を別のテーブルのすべての行と組み合わせるための洗練されたシンプルな手段を提供します。ただし、あるテーブルにあって別のテーブルにない値を見つけたい場合があります。今日のブログ記事で見るように、結合は、テーブルを結合するための述語を含めることによって、この目的にも利用できます。アンチジョインと呼ばれるこれらの結合は、次のようなさまざまなビジネス関連の質問に答えるのに役立ちます。

• どの顧客が注文をしなかったか？
• どの従業員が部署に配属されていないか？
• どの営業担当者が今週契約を成立させなかったか？

このブログでは、アンチジョインの種類と、PostgreSQLのdvdrental database例を使用してアンチジョインを記述する方法についての入門編を提供します。Navicat Premium Lite 17 でクエリを作成して実行します。

2種類のアンチジョイン

アンチジョインには2つの種類があります。

• 左アンチジョイン：右のテーブルに対応する行がない左のテーブルの行を返します。
• 右アンチジョイン：左のテーブルに対応する行がない右のテーブルの行を返します。

返される行は、下の図では青で示されています。

次のセクションでは、左アンチジョインを例に、アンチジョインを作成するために使用できるいくつかの異なる構文を順を追って説明します。

EXISTSを使用した左アンチジョイン

dvdrentalデータベース内の、どの映画にも出演していないすべての俳優を見つけたいとしましょう。残念ながら、SQLにはこの操作のための組み込み構文がありませんが、EXISTSを使用して、より具体的にはNOT EXISTSを使用してエミュレートできます。そのクエリは次のようになります。

SELECT *
FROM actor a
WHERE NOT EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Navicat Premium Lite 17で実行すると、次の結果が得られます。

NOT INに注意！

EXISTSとINは同等なので、NOT EXISTSとNOT INも同等であると結論付けたくなるかもしれませんが、これは常に当てはまるわけではありません！右のテーブル（この場合はfilm_actor）に外部キー（actor_id）に対するNOT NULL制約がある場合にのみ、これらは同等です。

この特定のケースでは、actor_id列にNOT NULL制約があるため、NOT INクエリは同じ結果を返します。

actor_id列でNULLが許可されている場合、空の結果セットが返されます。次のクエリでこれを確認できます。

SELECT *
FROM actor
WHERE actor_id NOT IN (1, 2, 3, 4, 5, NULL)

上記のクエリは、NULLがSQLではUNKNOWN値を表すため、行を返しません。 actor_idがUNKNOWN値を含む値のセットに含まれているかどうかを確信できないため、述語全体がUNKNOWNになります！

NOT IN構文によってもたらされる危険を回避する最も簡単な方法は、NOT EXISTSを使用することです。DBAがデータをロードするために一時的に制約をオフにする可能性があり、その間クエリが役に立たなくなるため、NOT NULL制約の存在に賭けることは実際には価値がありません。

代替構文

はじめにで触れたように、LEFT JOINとRIGHT JOINを使用してアンチジョインを実行することもできます。そのためには、IS NULL述語を含むWHERE句を追加する必要があります。その構文のLEFT JOINバージョンを次に示します。

SELECT a.*
FROM actor a
  LEFT JOIN film_actor fa
	  ON a.actor_id = fa.actor_id
WHERE fa.actor_id IS NULL

クエリオプティマイザはこれを ANTI JOIN 操作として認識しないため、LEFT/RIGHT JOIN 構文の実行速度が遅くなる可能性があることに注意してください。

まとめ

今日のブログでは、SQL 構文の 3つのバリエーションを使用して左アンチジョインをエミュレートする方法を学びました。これらのうち、NOT EXISTS は、ANTI JOIN の意図を最もよく伝え、最も高速に実行される傾向があるため、最初の選択肢となるはずです。

Navicat Premium Lite 17 をお試しになりませんか？Windows、macOS、および Linux オペレーティングシステムに対応した14日間のフル機能の無料トライアル版をダウンロードできます。