WITHを使ったサブクエリ

はじめに

SQLのWITH句の使い方について、備忘録としてまとめておきたいと思います。

WITH句の概要

PostgreSQL 9.3.2文書によると

WITHは、より大規模な問い合わせで使用される補助文を記述する方法を提供します。これらの文は共通テーブル式またはCTEとよく呼ばれるものであり、1つの問い合わせのためだけに存在する一時テーブルを定義するものと考えられます。WITH句内の補助文はそれぞれSELECT、INSERT、UPDATEまたはDELETEを取ることができます。そしてWITH句自身は、これもSELECT、INSERT、UPDATEまたはDELETEを取ることができる主文に付与されます。

とのことです...

ざっくりまとめると、

1. WITH句は大規模なクエリで使用されるサブクエリ
2. 1つのクエリに対し一時テーブルを定義する
3. WITH句内ではSELECT、INSERT、UPDATE、DELETE文を全て使用することができる

（と捉えています。間違いがあれば指摘お願いします。）

具体的な使用例

3つのテーブルを例にし、具体的な使い方を説明します。

前提条件

-- Areasテーブルの作成
CREATE TABLE Areas (
    area_id SERIAL PRIMARY KEY,
    area_name VARCHAR(100) NOT NULL,
    description TEXT
);
-- Areasテーブルにデータを挿入
INSERT INTO Areas (area_name, description) VALUES
('Fantasy Area', 'A magical place with fairy tale themes.'),
('Adventure Area', 'An exciting area with thrilling rides.'),
('Sci-Fi Area', 'A futuristic area with advanced technology.');

-- Attractionsテーブルの作成
CREATE TABLE Attractions (
    attraction_id SERIAL PRIMARY KEY,
    attraction_name VARCHAR(100) NOT NULL,
    area_id INTEGER NOT NULL,
    description TEXT,
    FOREIGN KEY (area_id) REFERENCES Areas(area_id)
);
-- Attractionsテーブルにデータを挿入
INSERT INTO Attractions (attraction_name, area_id, description) VALUES
('Castle Ride', 1, 'A ride through a fairy tale castle.'),
('Pirate Ship', 2, 'A swinging pirate ship ride.'),
('Haunted Mansion', 1, 'A spooky haunted mansion walkthrough.'),
('Wild River Rafting', 2, 'An exhilarating wild river rafting adventure.'),
('Dragon Coaster', 2, 'A high-speed roller coaster with dragon themes.'),
('Space Journey', 3, 'A space-themed virtual reality experience.');

-- AttractionUsersテーブルの作成
CREATE TABLE AttractionUsers (
    user_id SERIAL PRIMARY KEY,
    attraction_id INTEGER NOT NULL,
    user_name VARCHAR(100) NOT NULL,
    visit_date DATE NOT NULL,
    FOREIGN KEY (attraction_id) REFERENCES Attractions(attraction_id)
);
-- AttractionUsersテーブルにデータを挿入
INSERT INTO AttractionUsers (attraction_id, user_name, visit_date) VALUES
(1, 'visitor_1', '2024-06-01'),
(2, 'visitor_2', '2024-06-02'),
(3, 'visitor_3', '2024-06-03'),
(4, 'visitor_4', '2024-06-04'),
(5, 'visitor_5', '2024-06-05'),
(6, 'visitor_6', '2024-06-06'),
(3, 'visitor_7', '2024-06-07'),
(4, 'visitor_8', '2024-06-08'),
(5, 'visitor_9', '2024-06-09'),
(1, 'visitor_10', '2024-06-10'),
(2, 'visitor_11', '2024-06-11'),
(3, 'visitor_12', '2024-06-12'),
(1, 'visitor_13', '2024-06-13'),
(2, 'visitor_14', '2024-06-14'),
(3, 'visitor_15', '2024-06-15');

SELECTの使用

WITH句の中でのSELECTの使用は、以下のように行います。

WITH select_attractions AS (
    SELECT attraction_id AS id FROM Attractions WHERE area_id = 1
)
SELECT * FROM AttractionUsers WHERE attraction_id IN (SELECT id FROM select_attractions);

WITH句の中では、Attractionsテーブルの中から area_id = 1 つまりFantasy Areaにあるアトラクションを選び、一時的なテーブルであるselect_attractionsとして情報を保持します。その後、SELECT文でselect_attractionsテーブルのidカラムに存在する値と一致するattraction_idを持つUser情報を取得しています。

Areaの名前を検索値にしようとすると

WITH select_area AS (
    SELECT area_id FROM areas WHERE area_name = 'Fantasy Area'
),
select_attractions AS (
    SELECT attraction_id FROM Attractions WHERE area_id IN (SELECT area_id FROM select_area)
)
SELECT * FROM AttractionUsers WHERE attraction_id IN (SELECT attraction_id FROM select_attractions);

DELETEの使用

例えば「Sci-Fi Areaが閉鎖になったので、関連するデータを消したい」となったとき。このように消せます。

WITH select_area AS (
    SELECT area_id FROM areas WHERE area_name = 'Sci-Fi Area'
),
select_attractions AS (
    SELECT attraction_id FROM Attractions WHERE area_id IN (SELECT area_id FROM select_area)
),
delete_users AS (
    DELETE FROM AttractionUsers WHERE attraction_id IN (SELECT attraction_id FROM select_attractions)
),
delete_attractions AS (
    DELETE FROM Attractions WHERE area_id IN (SELECT area_id FROM select_area)
)
DELETE FROM areas WHERE area_name = 'Sci-Fi Area';

WITH句で繋げる際、（）の内側のサブクエリは上から順番に実行されていきます。外部制約キーを設定している際は、消したい順番に記述すればよいことになります。

WITH句を使用するメリット

読みやすい構文

WITH句を使用することのメリットはやはり視認性、可読性の高さだと思います。今回は3つのテーブルを関連させていますが、より多くのテーブルを組み合わせたり、複雑な検索条件になったりすると、実行文はかなり読みにくくなります。先ほどのSELECT文はこちらです。

WITH select_area AS (
    SELECT area_id FROM areas WHERE area_name = 'Fantasy Area'
),
select_attractions AS (
    SELECT attraction_id FROM Attractions WHERE area_id IN (SELECT area_id FROM select_area)
)
SELECT * FROM AttractionUsers WHERE attraction_id IN (SELECT attraction_id FROM select_attractions);

修飾節が目的語の前に付いているのが日本語の文法に似ている気がして、個人的にはわかりやすいです。「'Fantasy Area'にあるアトラクション（select_attractions）に乗ったことのあるユーザーを表示」という感じ。

一方、WITH句を使わないと

SELECT * FROM AttractionUsers WHERE attraction_id IN (SELECT attraction_id FROM Attractions WHERE area_id IN (SELECT area_id FROM Areas WHERE area_name = 'Fantasy Area'));

こちらは英語的な感じがします。「Users who have ridden attractions in Fantasy Area」という感じ。

DELETE, INSERT, UPDATEを複数使用可能

これも大きなメリットです。片方のテーブルだけでなく両方とも消したいという要望にも応えられます。

WITH句のデメリット

良いことだらけに見えるWITH句の使用ですが、デメリットとしてPCのメモリをより多く使ってしまう可能性があります。WITH句を使用すると仮想テーブルを作成するため、仮想テーブルのカラムは最小限に絞って使用するのが良さそうです。