レコードが存在するかの条件判定 EXISTS 演算子の利用 - SQL

SQLで条件にマッチするレコードが存在するか判定する場合のSQLについて紹介します。

概要

レコードが存在するかを条件にする場合は、EXISTS演算子を用います。

書式

where節で以下の書式を利用します。
レコードが存在しないことを条件にする場合は not exists が利用できます。

書式例

  select * from products where exists (select id from sale)

実行例1:動作確認の例

products

id	model	name	category	price
1	C-XM01	モーダンチェア	チェア	56000
2	X-XD05	ラージデスク	テーブル	87000
3	A-DA40	ラウンドダイニングチェア	チェア	28000

上記のテーブルが存在した場合

select * from products where exists (select top(0) * from products)

のSQLは何も結果を返しません。"select top(0) * from products"のSQLはレコードを全く返さないため、existsは常にfalseとなりすべてのレコードで条件にマッチしないため何も結果を返さない動作となります。

結果

id	model	name	category	price

実行例2:動作確認の例

products

id	model	name	category	price
1	C-XM01	モーダンチェア	チェア	56000
2	X-XD05	ラージデスク	テーブル	87000
3	A-DA40	ラウンドダイニングチェア	チェア	28000

上記のテーブルが存在した場合

select * from products where exists (select null)

のSQLを実行した場合、全ての行を返す結果となります。
"select null"はNULLの行を1つ返しますので、行は存在するため、existsは trueと判定されます。 すべての行で同じ結果(NULL)が返りますので、"select * from products"と同等のSQLとなり、全ての行が結果で返されます。

なお、以下のSQL文でもexists節内のselectで値が返るため、全行が結果で返されます。

select * from productsB where exists (select 1)

select * from productsB where exists (select '')

結果

id	model	name	category	price
1	C-XM01	モーダンチェア	チェア	56000
2	X-XD05	ラージデスク	テーブル	87000
3	A-DA40	ラウンドダイニングチェア	チェア	28000

実行例3:別のテーブルに存在する値を持つ行のみを取得

products

id	model	name	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

sale

id	category
1	チェア
2	ベッド

上記のテーブルが存在した場合に、以下のSQLを実行します。

select * from products where exists (select id from sale where products.category = sale.category)

または

select * from products as P where exists (select id from sale where P.category = category)

解説

select id from sale where P.category = category のサブクエリでは、メインのクエリの行のcategory(P.category)に一致するcategoryがsaleテーブルに存在するかを検索しています。メインのクエリのcategoryに一致するcategoryがsaleテーブルに存在すれば、saleテーブルの該当するcategoryの行が返り existsがtrueになります。
クエリ全体ではproductsテーブルのcategoryがsaleテーブルのcategoryに含まれているレコードを返します。

結果

id	model	name	category	price
1	C-XM01	モーダンチェア	チェア	56000
3	A-DA40	ラウンドダイニングチェア	チェア	28000
4	O-XX100	ナチュラルオフィス	チェア	13800
7	B-200	リネンベッド	ベッド	184500
8	B-250	ホワイトダブルベッド	ベッド	324850
9	W-80	ワーキングチェア	チェア	45000

実行例4 : not exists の例

products

id	model	name	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

sale

id	category
1	チェア
2	ベッド

上記のテーブルが存在した場合に、以下のSQLを実行します。

select * from ProductsB 
  where not exists (select id from sale where ProductsB.category = sale.category)

解説

例3と同じですが exits が not existsとなっています。
productsテーブルのcategoryがsaleテーブルのcategoryに含まれていないレコードを返します。

結果

以下の結果が得られます。

id	model	name	category	price
2	X-XD05	ラージデスク	テーブル	87000
5	R-D400	ラウンドダイニングテーブル	テーブル	128000
6	R7000	ウッドキャビネット	その他	32000
10	EG-10X	エルゴノミクスデスク	テーブル	88500

応用例

NOT EXISTSを利用する例として、グループごとに値の最大値のレコードを求める場合に利用します。詳細はこちらの記事を参照してください。