with句を利用したクエリ内テーブルの記述方法について紹介します。

概要

複雑なクエリを処理したい場合に、クエリを分割したり、テーブルから事前に該当するレコードを抜き出して、そのレコードに対してクエリを実行したい場合があります。 with文を利用すると、クエリ内テーブルを作成できます。

書式

with文の書式は以下です。

クエリ内テーブルを複数記述する場合は次の書式となります。

[クエリ]内で[クエル内テーブル名]を利用できます。

例

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

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
11	OC-908	オーガニックチェア	ホーム	チェア	56000

次のクエリを実行します。

with filter1 as (
select * from Products where category = 'チェア'
)
select * from filter1 where price > 40000

解説

with句でクエリ内テーブルを作成しています。元のProducts テーブルからcategory列の値が "チェア" のレコードを取り出して、filter1 とします。

with filter1 as (
select * from Products where category = 'チェア'
)

filter1 のクエリ内テーブルから、price列の値が 40000より大きいレコードを取り出してクエリの結果とします。

select * from filter1 where price > 40000

実行結果

クエリを実行すると、以下の結果となります。

id	model	name	class	category	price
1	C-XM01	モーダンチェア	ホーム	チェア	56000
9	W-80	ワーキングチェア	オフィス	チェア	45000
11	OC-908	オーガニックチェア	ホーム	チェア	56000

whereを一つの句にまとめた以下のクエリと同じ結果になります。

select * from Products where category = 'チェア' and price > 40000

また、サブクエリを利用した以下のSQLとも同じ結果になります。

select * from (select * from Products where category ='チェア') as f where price > 40000

with句のメリット

with句でできることは、where句にまとめることや、サブクエリ、副問い合わせで実現可能です。 with句のメリットとして、サブクエリではクエリ内にクエリを入れ子にして記述する必要がありますが、 with句であれば、クエリの手前に記述できるため、入れ子にせず記述でき、クエリの見通しが良くなります。