Oracle DatabaseのINTERSECTとは？複数SELECTの共通部分を取得する方法と使い方・注意点を解説

Oracle Databaseでは、複数のSELECT文から共通して存在する行だけを取得したい場合にINTERSECT演算子を使用できます。たとえば「商品Aを購入した顧客」と「商品Bを購入した顧客」の両方に含まれる顧客だけを抽出したい場合などに便利です。

INTERSECTはJOINとは用途が異なり、それぞれのSELECT文が返す結果セット同士を比較して、その共通部分を返します。2つのSELECTだけでなく、条件を満たせば複数のSELECTをINTERSECTでつなぐこともできます。

この記事では、Oracle DatabaseにおけるINTERSECTの基本構文、2つ・3つ以上のクエリで共通部分を求める具体例、重複行の扱い、UNIONやMINUS、JOINとの違い、利用時に注意したいデータ型やORDER BYについて分かりやすく解説します。

Oracle DatabaseではINTERSECTでSELECT結果の共通部分を取得できる

INTERSECTは、前後に記述したSELECT文の結果を比較し、両方の結果に存在する行だけを返す集合演算子です。

基本的な書き方は次のとおりです。

SELECT customer_id
FROM purchase_a
INTERSECT
SELECT customer_id
FROM purchase_b;

このSQLでは、purchase_aとpurchase_bの両方に存在するcustomer_idだけが結果として取得されます。

たとえばpurchase_aの検索結果が「101、102、103」、purchase_bが「102、103、104」であれば、INTERSECTの結果は「102、103」です。このように数学でいう集合の「積集合」に近い処理をSQLで実行できます。

INTERSECTの具体例｜2つのクエリに共通するデータを取得する

例として、顧客の購入履歴を保存するPURCHASE_HISTORYテーブルがあり、商品コードがPRODUCT_Aの商品を購入した顧客と、PRODUCT_Bを購入した顧客の両方を調べるケースを考えます。

SELECT customer_id
FROM purchase_history
WHERE product_code = 'PRODUCT_A'
INTERSECT
SELECT customer_id
FROM purchase_history
WHERE product_code = 'PRODUCT_B';

このSQLを実行すると、PRODUCT_Aを購入した顧客一覧とPRODUCT_Bを購入した顧客一覧の共通部分だけが返されます。

たとえばPRODUCT_Aを購入した顧客が「1、2、3、5」、PRODUCT_Bを購入した顧客が「2、3、4、5」なら、結果は「2、3、5」です。「AまたはB」ではなく、AにもBにも存在するデータを求めるのがINTERSECTです。

3つ以上のクエリでもINTERSECTを使用できる

INTERSECTは2つのSELECTだけに限定されません。3つ以上の検索結果について、すべてに共通する行を取得することもできます。

たとえば商品A、商品B、商品Cのすべてを購入した顧客を探す場合は次のように記述できます。

SELECT customer_id
FROM purchase_history
WHERE product_code = 'PRODUCT_A'
INTERSECT
SELECT customer_id
FROM purchase_history
WHERE product_code = 'PRODUCT_B'
INTERSECT
SELECT customer_id
FROM purchase_history
WHERE product_code = 'PRODUCT_C';

この場合、3つすべてのSELECT結果に含まれているcustomer_idのみが残ります。1つでも含まれていない顧客は結果から除外されます。

したがって「条件Aと条件Bと条件Cを、それぞれ独立した検索結果として作り、そのすべてに存在するデータを求めたい」という処理にINTERSECTを利用できます。

INTERSECTでは重複行がまとめられる

通常のINTERSECTでは、共通している行が複数回存在していても、結果では重複が取り除かれます。つまり集合として共通部分を求める動作になります。

たとえば1つ目のSELECTが「100、100、200、300」、2つ目が「100、100、100、300」だったとしても、通常のINTERSECT結果では「100、300」のように重複しない形で返されます。

同じcustomer_idが購入履歴に何件存在するかではなく、「その顧客が両方の検索結果に存在するか」を調べたい場合には、この動作が適しています。

INTERSECTを使うにはSELECTする列数を合わせる必要がある

Oracle Databaseの集合演算子を使用する場合、INTERSECTの前後にあるSELECT文は選択する列数を一致させる必要があります。

たとえば次のようなSQLは、左側が1列、右側が2列なので、そのままINTERSECTすることはできません。

SELECT customer_id
FROM table_a
INTERSECT
SELECT customer_id, customer_name
FROM table_b;

1列同士で比較するのであれば、双方をcustomer_idだけにそろえます。

SELECT customer_id
FROM table_a
INTERSECT
SELECT customer_id
FROM table_b;

複数列を比較したい場合は、双方で同じ数の列を並べます。INTERSECTでは一列だけではなく、複数列を組み合わせた行全体の共通部分も取得できます。

対応する列は互換性のあるデータ型にする

列数だけでなく、対応する列のデータ型にも注意が必要です。1つ目のSELECTの1列目と、2つ目のSELECTの1列目というように、同じ位置にある式同士が比較可能なデータ型でなければなりません。

たとえばcustomer_idを一方では数値としてSELECTし、もう一方ではまったく異なる種類のデータとして返すようなSQLは問題になる可能性があります。

異なるテーブル間でINTERSECTを使用するときは、列の意味だけでなくデータ型も確認しておくと安全です。必要であればCASTなどを使用してデータ型を明示的にそろえる方法があります。

複数列をINTERSECTすると「行全体」が一致する必要がある

INTERSECTで2列以上をSELECTした場合、それぞれの列を独立して比較するのではなく、SELECTされた行全体が共通しているかどうかで判定されます。

たとえば次のSQLを考えます。

SELECT customer_id, status
FROM table_a
INTERSECT
SELECT customer_id, status
FROM table_b;

table_aに「customer_id=100、status=’ACTIVE’」があり、table_bに「customer_id=100、status=’STOP’」しかない場合、customer_idは同じでもstatusが違うため、その行は共通部分にはなりません。

customer_idだけを基準に共通する顧客を調べたい場合は、SELECTする列もcustomer_idだけにします。何を共通条件として比較したいのかによってSELECT列を決めることが重要です。

INTERSECTとUNIONの違い

INTERSECTと混同しやすい集合演算子がUNIONです。両者は目的が反対に近い関係にあります。

たとえばAの結果が「1、2、3」、Bの結果が「3、4、5」なら、INTERSECTは「3」、UNIONは「1、2、3、4、5」、MINUSでAからBを引くと「1、2」となります。

「両方にあるものを探す」のがINTERSECTだと覚えておけば、用途を判断しやすくなります。

INTERSECTとINNER JOINは似ているが用途が違う

共通するデータを取得すると聞くとINNER JOINを思い浮かべることもありますが、INTERSECTとJOINは目的が異なります。

INTERSECTは完成した2つの検索結果そのものの共通行を求める処理です。一方JOINは、複数のテーブルをキーなどの条件で関連付け、新しい一つの結果セットを作る処理です。

たとえば顧客テーブルと注文テーブルから、顧客名と注文金額を一緒に取得したいならJOINが適しています。

SELECT c.customer_name, o.amount
FROM customers c
INNER JOIN orders o
  ON c.customer_id = o.customer_id;

一方、「条件Aで抽出した顧客ID一覧」と「条件Bで抽出した顧客ID一覧」の両方に入っているIDを取得したい場合はINTERSECTが直感的です。

INTERSECTとAND条件は同じではない

単一テーブルから条件を絞り込むのであれば、WHERE句のANDだけで済む場合もあります。しかし条件によってはINTERSECTのほうが自然に表現できます。

たとえば同一行のpriceが1000円以上でstatusがACTIVEという条件なら、次のようにANDを使えば十分です。

SELECT product_id
FROM products
WHERE price >= 1000
  AND status = 'ACTIVE';

一方、購入履歴のように「商品Aを購入した行」と「商品Bを購入した行」が別々に存在する場合、単純に次のようなANDを書くことはできません。

WHERE product_code = 'A'
  AND product_code = 'B'

1つのproduct_codeが同時にAとBになることはないためです。このような場合には、それぞれの購入者をSELECTしてINTERSECTする方法が分かりやすくなります。

EXISTSやGROUP BYで同じ目的を実現できる場合もある

INTERSECTで書ける処理は、必ずINTERSECTでなければ実現できないわけではありません。EXISTSやJOIN、GROUP BYなどで書き換えられるケースもあります。

たとえば商品Aと商品Bの両方を購入した顧客なら、GROUP BYとHAVINGを利用して次のように表現する方法もあります。

SELECT customer_id
FROM purchase_history
WHERE product_code IN ('PRODUCT_A', 'PRODUCT_B')
GROUP BY customer_id
HAVING COUNT(DISTINCT product_code) = 2;

条件となる商品が同じテーブルの同じ列に入っているケースでは、この書き方のほうが管理しやすい場合もあります。

どのSQLが高速かは、テーブルサイズ、インデックス、統計情報、条件、Oracle Databaseの実行計画などによって変わるため、「INTERSECTなら必ず速い」「JOINなら必ず速い」と一律には判断できません。性能が重要な処理では実際の実行計画を確認します。

ORDER BYは通常、集合演算した最終結果に対して指定する

INTERSECTの結果を並べ替えたい場合は、通常は集合演算全体の最後にORDER BYを指定します。

SELECT customer_id
FROM table_a
INTERSECT
SELECT customer_id
FROM table_b
ORDER BY customer_id;

これにより、INTERSECTで共通部分を求めた後、その最終結果をcustomer_id順に並べることができます。

INTERSECT自体は結果の表示順を保証するための機能ではありません。そのため「今実行したら番号順に表示された」という場合でも、必要な並び順があるならORDER BYを明示することが大切です。

UNION・INTERSECT・MINUSを混在させる場合は括弧で意図を明確にする

実務では、INTERSECTだけでなくUNIONやMINUSなど複数の集合演算子を同じSQLで使う場合があります。このようなSQLは、処理順序を人が読み違えやすくなります。

たとえば「AとBの共通部分」とCを結合したい場合などは、サブクエリを利用して処理単位を明確にするほうが保守しやすくなります。

SELECT id
FROM (
  SELECT id FROM table_a
  INTERSECT
  SELECT id FROM table_b
)
UNION
SELECT id
FROM table_c;

集合演算子を複雑に連結するSQLでは、仕様上の評価順序だけに頼るより、サブクエリなどで意図した処理順を明示することで、将来コードを読む人にも分かりやすくなります。

INTERSECTが便利な実務例

INTERSECTは、「別々の条件で作られる対象者一覧の共通部分」を求める処理と相性がよい演算子です。

たとえばECサイトなら、「過去1年間に商品Aを購入した顧客」と「過去1年間に商品Bを購入した顧客」の両方を満たす顧客を抽出する用途があります。

会員システムなら、「有料会員一覧」と「キャンペーン応募者一覧」の共通会員だけを取得するといった用途にも利用できます。

権限管理では、「部署Aに所属するユーザー」と「特定資格を持つユーザー」の共通ユーザーを、それぞれ独立したクエリ結果として比較する設計も考えられます。

複雑なJOINで一度に表現しようとするとSQLの目的が分かりにくくなる場合でも、INTERSECTなら「この一覧とこの一覧の共通部分」という形で読みやすく記述できることがあります。

INTERSECTを使うときのチェックポイント

Oracle DatabaseでINTERSECTを使用するときは、次の点を確認するとエラーや意図しない結果を防ぎやすくなります。

特に初めて使用するときは、「共通にしたい項目以外までSELECTしていないか」を確認することが重要です。余分な列を追加すると、その列まで一致しなければ共通行と判断されなくなるためです。

まとめ：Oracle DatabaseではINTERSECTで複数クエリの共通部分を取得できる

Oracle Databaseでは、INTERSECTを使用して複数のSELECT結果に共通する行を取得できます。2つだけでなく、3つ以上のSELECTをINTERSECTでつないで、すべての結果に存在するデータを抽出することも可能です。

基本形は「SELECT … INTERSECT SELECT …」で、通常のINTERSECTでは重複する共通行はまとめられます。また各SELECTでは列数をそろえ、対応する位置の列を互換性のあるデータ型にする必要があります。

INTERSECTは「別々に作った検索結果の共通部分」を表現したいときに特に分かりやすい方法です。一方、テーブル同士から関連する列を取得するならJOIN、同じテーブル内の複数条件を集計するならGROUP BYやEXISTSのほうが適する場合もあります。

SQLを選ぶ際には、単に書けるかどうかだけでなく、何と何の共通部分を求めているのかが読み手に明確かという点も重要です。独立した複数クエリの結果セット同士を比較したい場面では、INTERSECTはOracle Databaseで非常に分かりやすい選択肢になります。