結果からレコードを除外する (EXCEPT句の利用) - SQL

EXCEPT句を利用して結果からレコードを除外するSQL文を紹介します。

概要

SELECT文で取得した結果のレコードセットから特定のレコードを除外したい場合があります。
EXCEPT句を利用すると、結果からレコードを除外する処理が実現できます。

書式

次の書式を利用します。 [結果セット] と [除外する結果セット] の列数は同じ数にする必要があります。

記述例

SELECT name,code FROM UserList
EXCEPT
SELECT name,code FROM BlackList

例1

次のテーブルを用意します。

Products テーブル

id	name	price	category
1	Penguin	500	Bird
2	Bear	1050	Mammal
3	Duck	150	Bird
4	Camel	920	Mammal
5	Owl	185	Bird
6	Whale	880	Mammal

ProductsEX テーブル

id	name	price	category
1	Penguin	500	Bird

次のSQLを実行します。

SELECT * FROM Products
EXCEPT
SELECT * FROM ProductsEX

以下の結果になります。 Products レコード全件の中から、ProducsEXレコードの id=1, name="Penguin", price=500, category="Bird" のレコードが除外され、結果が返されます。

id	name	price	category
2	Bear	1050	Mammal
3	Duck	150	Bird
4	Camel	920	Mammal
5	Owl	185	Bird
6	Whale	880	Mammal

例2

先の例で、EXCEPT の基本的な動作を紹介しました。 先の例で紹介したテーブルではidの値は自動採番される場合が多いため、完全に同じ値のレコードは実際には作成するケースが少ないです。 また、除外したいテーブルに元のテーブルと同じ情報をすべて記録するケースもあまりなく、除外したいidのみを記述するケースが多いです。 この例では、除外用のテーブルに除外したいidのみを記述するケースでの利用方法を紹介します。

次のテーブルを用意します。

Products

id	model	name	class	category	price
1	C-XM01	モーダンチェア	ホーム	チェア	56000
2	X-XD05	ラージデスク	オフィス	テーブル	87000
3	A-DA40	ラウンドダイニングチェア	ホーム	チェア	28000
4	O-XX100	ナチュラルオフィス	オフィス	チェア	13800
5	R-D400	ラウンドダイニングテーブル	ホーム	テーブル	128000
6	R7000	ウッドキャビネット	オフィス	その他	32000
7	B-200	リネンベッド	ホーム	ベッド	184500
8	B-250	ホワイトダブルベッド	ホーム	ベッド	324850
9	W-80	ワーキングチェア	オフィス	チェア	45000
10	EG-10X	エルゴノミクスデスク	オフィス	テーブル	88500

除外するアイテムのテーブルです。除外アイテムのidのみをexid列に記録しています。

ProductsEX

id	exid
1	4
2	7
3	9

以下のSQL文を実行します。

select * from (
SELECT id FROM Products
EXCEPT
SELECT exid FROM ProductsEX
)
as F inner join Products on F.id = Products.id

解説

サブクエリで、Productsテーブルのレコードのidと除外レコードが記録されているProductsEXレコードを列挙し、EXCEPTで、 ProductsテーブルのレコードからProductsEXレコードの項目を除外した結果を作成します。

SELECT id FROM Products
EXCEPT
SELECT exid FROM ProductsEX

上記のSQLの結果は以下になります。

id
1
2
3
5
6
8
10

上記のidに対してProductsテーブルのidとjoinして、Products テーブルのレコードから、ProductsEXレコードのexidを除外したレコード一覧を取得できます。

実行結果

SQLの結果は以下になります。

id	id	model	name	class	category	price
1	1	C-XM01	モーダンチェア	ホーム	チェア	56000
2	2	X-XD05	ラージデスク	オフィス	テーブル	87000
3	3	A-DA40	ラウンドダイニングチェア	ホーム	チェア	28000
5	5	R-D400	ラウンドダイニングテーブル	ホーム	テーブル	128000
6	6	R7000	ウッドキャビネット	オフィス	その他	32000
8	8	B-250	ホワイトダブルベッド	ホーム	ベッド	324850
10	10	EG-10X	エルゴノミクスデスク	オフィス	テーブル	88500