MySQL の交差演算子

INTERSECT は、2つのクエリ結果セットの両方に含まれる行を返します。MySQLで使うには、まず使用中のサーバーがネイティブ構文を受け付けるか確認してください。受け付けない場合は、INNER JOIN、IN、または EXISTS を使ったクエリで共通部分を表現できます。必要な列と、一致する行の重複を表示するかどうかに応じて選びましょう。

INTERSECT が結果セットを組み合わせる仕組み

それぞれの SELECT が行の集合を生成すると考えてください。INTERSECT は、どちらか一方にあるすべての行ではなく、両方に共通する行を残します。図では、その共通領域が強調されています。

たとえば、顧客一覧と注文一覧から顧客 ID を取り出して共通部分を求めると、両方の結果に含まれる顧客が残ります。これは選択した値に対する集合演算です。結合条件が交差に含める行を定義する値と一致する場合、JOIN で同様の処理を表現できます。一方、異なる列を選択すると、同一の行の共通部分を再現するのではなく、関連するレコードを組み合わせることになります。

使用中の MySQL バージョンで INTERSECT が使えるか確認する

対応状況は実際に稼働しているサーバーのバージョンによって異なるため、一律の説明や未確認のリリース時期を前提にしないでください。インストール済みバージョンのドキュメントを確認するか、開発環境で SELECT 1 INTERSECT SELECT 1; のような最小限のクエリを試してください。サーバーが構文を拒否した場合は、以下のいずれかのパターンを使います。ここに示すSQLは構文例であり、MySQL サーバーが必要です。提供された実行環境では実行されていません。

サンプルテーブルを準備する

例では、顧客とその注文を扱います。テーブル名には orders を使います。ORDER はSQLキーワードなので、引用符で囲まずにテーブル名として使うと問題が起きやすいためです。customer は CREATE TABLE customer (customer_id INT PRIMARY KEY, customer_firstname VARCHAR(60) NOT NULL, customer_lastname VARCHAR(60) NOT NULL, customer_age INT NOT NULL, customer_salary INT NOT NULL); で作成します。orders は CREATE TABLE orders (order_id INT PRIMARY KEY, order_date DATETIME NOT NULL, order_amount INT NOT NULL, customer_id INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customer(customer_id)); で作成します。

サンプルの顧客データは INSERT INTO customer VALUES (1, 'Shajeel', 'Daniel', 23, 9000), (2, 'Nayya', 'Preston', 54, 1500), (3, 'James', 'Robert', 36, 2500), (4, 'Jennifer', 'John', 29, 5000), (5, 'Sarah', 'Paul', 15, 8000), (6, 'Karen', 'Donald', 40, 3500); で追加します。続いて、注文データを INSERT INTO orders VALUES (1, '2019-12-03 10:25:30', 500, 2), (2, '2019-12-10 12:00:30', 1500, 4); で追加します。注文列の順序は order_id, order_date, order_amount, customer_id です。

INTERSECT を INNER JOIN、IN、EXISTS でエミュレートする

集合の共通部分を示す一般的な例として、次の自己完結したPython例では、2つの入力結果に含まれるIDを取り出します。これは実行済みのPython例であり、MySQLへのクエリではありません。出力は下に示されています。SQLの各例は構文を示すもので、提供された実行環境では実行されていません。次の例を実行してください。

このセクションのコードと表示されるラベルや出力は英語のままです。

customers = {1, 2, 3, 4}
ordered_customers = {2, 4, 6}
print(sorted(customers & ordered_customers))

[2, 4]

両方のテーブルの列が必要な場合は、INNER JOIN を使います。次の構文は、一致する顧客の情報と注文の詳細を返します。SELECT o.order_id, c.customer_id, c.customer_firstname, c.customer_lastname, o.order_amount, o.order_date FROM orders AS o INNER JOIN customer AS c ON o.customer_id = c.customer_id; サンプルデータでは、顧客 ID 2と4が一致します。顧客に複数の注文がある場合、この結合は一致する注文ごとに1行を返します。集合演算のように、自動的に顧客ごとに1行になるわけではありません。

結果に顧客の列を含め、もう一方の結果に値が存在するかだけを調べる場合は、IN を使います。SELECT c.customer_id, c.customer_firstname, c.customer_lastname, c.customer_age FROM customer AS c WHERE c.customer_id IN (SELECT o.customer_id FROM orders AS o); このクエリは顧客2と4をそれぞれ1回返します。IN は一致する値が存在するかを調べるため、どちらかの顧客に複数の注文があっても結果は重複しません。

同じ存在確認に加えて顧客行の条件も指定する場合は、EXISTS を使います。SELECT c.customer_id, c.customer_firstname, c.customer_lastname, c.customer_age FROM customer AS c WHERE c.customer_age < 45 AND EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id); このサンプルデータでは顧客4が返されます。顧客2は54歳です。EXISTS は存在確認を直接表現し、一致する注文が複数あっても顧客行を重複させません。

用途に合ったエミュレーション方法を選ぶ

両側の列が必要な場合や、一致する組み合わせをそれぞれ扱いたい場合は INNER JOIN を選びます。出力元が一方のテーブルだけで、簡潔な存在確認フィルターが必要なら IN が適しています。ほかの条件と組み合わせる場合など、条件を相関サブクエリとして表すのが自然なら EXISTS を選びます。結合キーが一意でない場合、JOIN は行を重複して返すことがあります。存在確認には IN または EXISTS を使うか、投影結果を一意にする必要がある場合は意図的に DISTINCT を適用してください。クエリを採用する前に、選択列と重複の扱いが INTERSECT に期待する結果と一致するか確認しましょう。