Caution

お使いのブラウザはJavaScriptが実行できない状態になっております。
当サイトはWebプログラミングの情報サイトの為、
JavaScriptが実行できない環境では正しいコンテンツが提供出来ません。
JavaScriptが実行可能な状態でご閲覧頂くようお願い申し上げます。

SQL辞典

トップページ
SQL辞典
INTERSECT / EXCEPT

INTERSECT / EXCEPT

2つのSELECT結果の共通行（INTERSECT）や差分行（EXCEPT / MINUS）を取り出す集合演算子です。どちらも重複行は除去されます。

構文

-- INTERSECT: 両方のSELECTに共通する行を返します。
SELECT 列名 FROM テーブルA
INTERSECT
SELECT 列名 FROM テーブルB;

-- EXCEPT: 左のSELECTにあって右にない行を返します（SQL標準）。
SELECT 列名 FROM テーブルA
EXCEPT
SELECT 列名 FROM テーブルB;

-- MINUS: EXCEPT と同義です（Oracle では MINUS を使います）。
SELECT 列名 FROM テーブルA
MINUS
SELECT 列名 FROM テーブルB;

構文一覧

構文	概要
INTERSECT	2つのSELECT結果の共通行を返します。重複は除去されます。
EXCEPT	左のSELECT結果から右のSELECT結果に含まれる行を除いた差分行を返します（PostgreSQL・SQL Server・SQLiteなど）。
MINUS	EXCEPT と同義です（Oracle・DB2で使用）。

サンプルコード

-- 1月と2月の両方に注文した顧客IDを取得します（INTERSECT）。
SELECT 顧客ID
FROM 注文
WHERE MONTH(注文日) = 1
INTERSECT
SELECT 顧客ID
FROM 注文
WHERE MONTH(注文日) = 2;

-- 1月に注文したが2月には注文しなかった顧客を取得します（EXCEPT）。
SELECT 顧客ID
FROM 注文
WHERE MONTH(注文日) = 1
EXCEPT
SELECT 顧客ID
FROM 注文
WHERE MONTH(注文日) = 2;

-- MySQL は INTERSECT / EXCEPT が使えないため IN / NOT IN で代替します。
-- INTERSECT の代替。
SELECT DISTINCT 顧客ID FROM 注文 WHERE MONTH(注文日) = 1
  AND 顧客ID IN (SELECT 顧客ID FROM 注文 WHERE MONTH(注文日) = 2);

-- EXCEPT の代替。
SELECT DISTINCT 顧客ID FROM 注文 WHERE MONTH(注文日) = 1
  AND 顧客ID NOT IN (SELECT 顧客ID FROM 注文 WHERE MONTH(注文日) = 2);

実行結果

-- 1月と2月の両方に注文した顧客ID（INTERSECT）。
顧客ID
-------
1001
1005
1012

-- 1月のみ注文した顧客ID（EXCEPT）。
顧客ID
-------
1003
1008

データベース別の書き方

PostgreSQL・SQL Server・SQLite は『INTERSECT』・『EXCEPT』をそのまま使用できます。

-- PostgreSQL・SQL Server・SQLite
SELECT 顧客ID FROM 注文 WHERE MONTH(注文日) = 1
INTERSECT
SELECT 顧客ID FROM 注文 WHERE MONTH(注文日) = 2;

Oracle では『EXCEPT』の代わりに『MINUS』を使用します。『INTERSECT』は同じ構文で使えます。

-- Oracle では EXCEPT の代わりに MINUS を使用
SELECT 顧客ID FROM 注文 WHERE EXTRACT(MONTH FROM 注文日) = 1
MINUS
SELECT 顧客ID FROM 注文 WHERE EXTRACT(MONTH FROM 注文日) = 2;

MySQL は 8.0.31 以降で『INTERSECT』・『EXCEPT』をサポートしています。それ以前のバージョンでは『IN』・『NOT IN』で代替してください。

-- MySQL（8.0.31 未満）での INTERSECT 代替
SELECT DISTINCT 顧客ID FROM 注文 WHERE MONTH(注文日) = 1
  AND 顧客ID IN (SELECT 顧客ID FROM 注文 WHERE MONTH(注文日) = 2);

-- MySQL（8.0.31 未満）での EXCEPT 代替
SELECT DISTINCT 顧客ID FROM 注文 WHERE MONTH(注文日) = 1
  AND 顧客ID NOT IN (SELECT 顧客ID FROM 注文 WHERE MONTH(注文日) = 2);

概要

『INTERSECT』と『EXCEPT』はSQLの集合演算子で、重複行は自動的に除去されます（UNION と同じ挙動）。列数とデータ型の互換性は UNION と同様に必要です。

MySQLは8.0.31以降でINTERSECT・EXCEPTをサポートしています。それ以前のバージョンでは IN・NOT IN・EXISTS・NOT EXISTS を使って代替してください。Oracleでは EXCEPT の代わりに MINUS を使います。

集合演算子の仲間である UNION・UNION ALL の使い方は『UNION / UNION ALL』を参照してください。

記事の間違いや著作権の侵害等ございましたらお手数ですがこちらまでご連絡頂ければ幸いです。

トップページへ

WebProgrammingPlaceウェブプログラミングプレイス