先生と生徒の会話形式で理解しよう

生徒

「PostgreSQLの勉強を始めたのですが、複雑な計算をするときに『サブクエリ』や『CTE』という言葉が出てきて混乱しています。どちらを使えばいいのでしょうか？」

先生

「それは中級者への第一歩ですね！サブクエリは命令の中に別の命令を入れ込む方法、CTEは一時的に名前を付けた表を作る方法です。実はこれ、見た目だけでなく実行スピード（パフォーマンス）にも違いがあるんですよ。」

生徒

「スピードも変わるんですか？パソコンの操作もあまり詳しくないので、できるだけ簡単に教えてほしいです！」

先生

「もちろんです。例えば『名簿の中から特定のグループを探して、その中からさらに条件に合う人を抽出する』といった作業を、紙の書類を整理する感覚で例えながら解説しますね。」

1. データベースとPostgreSQLの基本をおさらい

まずは、私たちが操作する「データベース」が何なのかを簡単に説明します。データベースとは、大量の情報を整理して保管しておくための、いわば「デジタルな本棚」や「巨大な名簿箱」のことです。PostgreSQL（ポストグレスキューエル、通称ポスグレ）は、その名簿箱を管理するための非常に賢いソフトウェアの名前です。

この名簿箱に対して、「〇〇さんの電話番号を教えて」「20歳以上の人をリストアップして」といった命令を出すための言葉が、SQL（エスキューエル）です。今回は、その命令の中でも少し高度な、条件を二段階に分けて指定するような書き方を学びます。

2. サブクエリ（副問合せ）とは？

サブクエリとは、SQLのメインの命令文の中に、もう一つ別の命令文を「入れ子」にして書く手法のことです。日本語で例えると、「『昨日のテストで80点以上取った人』の、名前と住所を教えて」という文章のようなものです。この「昨日のテストで80点以上取った人」という条件の部分がサブクエリにあたります。

例えば、注文データが入った「orders（オーダーズ）」というテーブルと、商品名が入った「products（プロダクツ）」というテーブルがあるとしましょう。「お菓子カテゴリーの商品を買った注文だけを見たい」という場合、まず商品テーブルでお菓子のIDを調べ、そのIDを使って注文テーブルを検索します。このように、一つの命令で二つのステップをこなすのがサブクエリの役割です。

3. IN句を使ったサブクエリの具体例

もっともよく使われるのが「IN（イン）」という言葉を使ったサブクエリです。これは「リストの中に含まれているもの」を探すときに使います。実際のデータを見てみましょう。ここでは「部署（departments）」と「社員（employees）」のデータを使います。


id | name      | department_id
---+-----------+--------------
1  | 田中太郎  | 1
2  | 佐藤花子  | 2
3  | 鈴木一郎  | 1
4  | 高橋良子  | 3
5  | 伊藤健太  | 2
6  | 渡辺真一  | 1

「営業部（department_idが1）」に所属する社員の名前だけを取得したいとき、以下のように書きます。


SELECT name
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE name = '営業部'
);

実行した結果は以下の通りです。


name
----------
田中太郎
鈴木一郎
渡辺真一

この書き方は直感的で分かりやすいのですが、データが増えてくると「名簿を一回一回めくって確認する」ような手間が発生し、パソコンの動作が重くなる原因になることがあります。

4. CTE（WITH句）の仕組みと書き方

次に、最近のプログラミング現場で推奨されることが多い「CTE（シーティーイー）」について解説します。CTEは「Common Table Expression」の略で、日本語では「共通テーブル式」と呼びます。難しい言葉ですが、簡単に言うと「一時的に、使い捨てのメモ用紙に結果を書いて名前をつけておく」という機能です。

先ほどのサブクエリは命令の中に命令が埋まっていて読みづらかったですが、CTE（WITH句）を使うと、まず「営業部のリスト」という名前のメモを作り、それを使って後から検索するという流れになります。文章がスッキリ整理されるのが大きな特徴です。


WITH sales_dept AS (
    SELECT id
    FROM departments
    WHERE name = '営業部'
)
SELECT e.name
FROM employees e
WHERE e.department_id IN (SELECT id FROM sales_dept);

実行結果は先ほどと同じですが、最初に「sales_dept（セールス・デプト）」という名前で絞り込みを行っているため、後から読んだときに何をしているかが一目でわかります。これは、プログラムが長くなったときに非常に役立ちます。

5. サブクエリとCTEのパフォーマンス比較

さて、ここが本題です。PostgreSQLにおいて、サブクエリとCTEはどちらが「速い」のでしょうか？昔のバージョンのPostgreSQLでは、CTEを使うと「一旦すべてのデータを計算し終えてから次に進む」という動き（マテリアライズ化）をしていたため、サブクエリの方が効率が良い場合がありました。

しかし、最新のPostgreSQL（バージョン12以降）では、CTEも賢く最適化（インライン化）されるようになりました。そのため、基本的には「どちらを使っても速度はほぼ変わらない」というのが現在の結論です。ただし、大量のデータ（数百万件など）を扱う場合や、複雑な計算を何度も使い回す場合は、CTEの方が全体の整理がつきやすく、結果としてミスが減り、管理がしやすくなるというメリットがあります。

逆に、非常にシンプルな一段階の絞り込みであれば、サブクエリの方がわずかに記述が短く済むため、使い分けが重要になります。

6. データが大量にある場合のINサブクエリの注意点

初心者がハマりやすい落とし穴として、IN句を使ったサブクエリの中に大量のデータが入ってしまうパターンがあります。例えば、10万件の商品IDをIN句の中に詰め込んでしまうと、PostgreSQLはその10万件を一つずつチェックしようとして、非常に時間がかかってしまいます。

このような場合は、「EXISTS（イグジスツ）」という言葉を使ったり、テーブル同士を「JOIN（ジョイン：結合）」させたりする方が速くなることが多いです。パソコンにも得意不得意があり、「リストから探す」よりも「二つの表をくっつけて横に並べる」方が得意な場合があるのです。


id | product_name | price
---+--------------+-------
1  | リンゴ       | 150
2  | バナナ       | 100
3  | メロン       | 2000
4  | イチゴ       | 500

価格が500円以上の商品が売れた注文を探す例です。


SELECT *
FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM products p
    WHERE p.id = o.product_id
    AND p.price >= 500
);

この「EXISTS」は、「条件に合うものが一つでも見つかったら即座に終了する」という賢い探し方をするため、効率が良いのです。

7. CTEを使う最大のメリット「可読性」

パフォーマンスの差が縮まった現在、プログラマーがCTEを選ぶ一番の理由は「可読性（かどくせい）」です。可読性とは、人間がそのプログラムを読んだときに「理解しやすいかどうか」という指標です。プログラムは一度書いたら終わりではなく、数ヶ月後に別の人が修正したり、間違い（バグ）を探したりすることがよくあります。

CTEを使うと、まるで料理のレシピのように「まず野菜を切ります（工程A）」「次に肉を炒めます（工程B）」「最後にAとBを混ぜます」といった具合に、順番に処理を記述できます。これは、複雑な集計（月ごとの売上を計算し、その平均を出し、さらに前年と比較するなど）を行う際に、頭の中を整理する強力な助けとなります。

8. どちらを使うべき？判断の基準

最後に、初心者の皆さんが迷わないための判断基準をまとめます。

サブクエリを使うべき時：
- 条件が非常にシンプルで、一行や数行で終わる場合。
- 「WHERE ID IN (...)」のように、定形文として使い慣れている場合。
CTE（WITH句）を使うべき時：
- 同じ中間結果を、一つの命令の中で二回以上使いたい時。
- 命令が長くなり、カッコの階層が深くなって「何を書いているか分からなくなった」時。
- 後で自分や他の人がプログラムを読み返すことが予想される時。

まずはCTEで書いてみて、処理の流れを整理する癖をつけるのが、上達への近道ですよ！

9. 実際のデータ操作でCTEを体験しよう

理解を深めるために、少し複雑な例を見てみましょう。会員ごとの「平均購入金額」を算出し、その平均よりも高い金額を支払っている「優良顧客」を探す操作をイメージしてください。サブクエリだと非常に読みづらくなりますが、CTEならスマートに書けます。


user_id | amount
--------+-------
1       | 5000
2       | 1500
3       | 8000
1       | 2000
2       | 1000


WITH user_scores AS (
    -- ユーザーごとの合計金額を計算
    SELECT user_id, SUM(amount) as total
    FROM sales
    GROUP BY user_id
),
average_score AS (
    -- 全体の平均金額を計算
    SELECT AVG(total) as avg_total
    FROM user_scores
)
SELECT u.user_id, u.total
FROM user_scores u, average_score a
WHERE u.total > a.avg_total;

このように、段階を踏んでデータを加工していく様子がわかりますね。これこそが、PostgreSQLを使いこなすための強力な武器になります。


user_id | total
--------+-------
1       | 7000
3       | 8000

複雑に見えますが、一つ一つの「WITH」の中身は単純な表の抜き出しです。一歩ずつ階段を登るように理解していけば、どんな難しいデータ分析もできるようになります。

PostgreSQL初心者必見！サブクエリとCTE（WITH句）の使い分けとパフォーマンス徹底比較