PostgreSQL: PostgreSQLのサブクエリとCTE

Q: CTEとサブクエリにパフォーマンス差はありますか？

PostgreSQL 12+はCTEをインライン化するか自動判断します。単純なCTEは通常インライン化され、複雑なものは実体化されます。MATERIALIZED/NOT MATERIALIZEDで手動制御してください。

Q: 再帰CTEは無限ループする可能性がありますか？

可能性があります。データに循環（例: A→B→A）が含まれる場合、再帰は停止しません。レベル制限を追加するか、パスを追跡してノードの再訪を回避してください。

Q: NOT INがNULLに当たると空を返すのはなぜですか？

x NOT IN (a, NULL) は x<>a AND x<>NULL と同等で、 x<>NULL はUNKNOWNです。AND連鎖ではUNKNOWNが行全体をFALSEにします。代わりにNOT EXISTSの方が安全です。

Q: CTEは前に定義されたCTEを参照できますか？

はい。 WITH a AS (...), b AS (SELECT ... FROM a) では、bはaを参照できます。定義順に下方向に参照します。

最終更新：2026-08-26

1. 学習目標

スカラー / カラム / 行 / テーブルサブクエリ
EXISTS / NOT EXISTS
ANY / ALL演算子
サブクエリの出現場所: FROM / WHERE / SELECT
CTE（WITH句、PostgreSQLの機能）
再帰CTE（WITH RECURSIVEツリークエリ）
CTEとサブクエリと一時テーブルの比較

2. ストーリー

AliceはSaaS企業の人事システム開発者です。この会社には500人以上の従業員がおり、ツリー構造で組織されています。CEOがトップで、その下にVP、VPの下にディレクター、ディレクターの下にマネージャー、マネージャーの下に従業員がいます。

プロダクトマネージャーから��の依頼がありました。任意のノードから開始し、そのノードとすべての子孫を（複数階層にわたって）リストアップし、インデント付きで階層を表示してください。

単純なクエリでは1階層しか辿れません。AliceはWITH RECURSIVE再帰CTEを学び、1つのSQL文でサブツリー全体を解決します。

3. 概念

(1) サブクエリの分類

型	戻り値	出現可能な場所	例
スカラーサブクエリ	1行1値	SELECT, WHERE, HAVING	`(SELECT MAX(salary) ...)`
カラムサブクエリ	1カラム複数行	WHERE + IN/ANY/ALL	`WHERE id IN (SELECT ...)`
行サブクエリ	1行複数カラム	WHERE	`WHERE (a,b) = (SELECT x,y ...)`
テーブルサブクエリ	複数行複数カラム	FROM	`FROM (SELECT ...) AS t`

▶ サンプル: SELECT内のスカラーサブクエリ

SQL

SELECT
  name,
  salary,
  (SELECT AVG(salary) FROM employees) AS company_avg,
  salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees
WHERE department_id = 5;

TEXT 📖 参照専用

 name    | salary | company_avg | diff
---------+--------+-------------+-------
 Alice   |  95000 |    72000.00 | 23000
 Bob     |  88000 |    72000.00 | 16000

▶ サンプル: WHERE内のスカラーサブクエリ

SQL

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

▶ サンプル: カラムサブクエリ + IN

SQL

SELECT order_id, amount
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE region = 'NA'
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: 行サブクエリ

SQL

SELECT name, department_id, salary
FROM employees
WHERE (department_id, salary) = (
  SELECT department_id, MAX(salary)
  FROM employees
  GROUP BY department_id
  HAVING department_id = employees.department_id
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) EXISTS / NOT EXISTS

EXISTSはサブクエリが行を返すかどうかをチェックします。実際の値は気にせず、「存在」のみを確認します。

▶ サンプル: EXISTSで注文のあるユーザーを検索

SQL

SELECT u.user_id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.user_id
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

アプローチ	構文	停止条件	NULL耐性
IN	`WHERE id IN (SELECT ...)`	全件スキャン	NULLに注意
EXISTS	`WHERE EXISTS (SELECT 1 ...)`	最初の行で停止	NULLの影響なし
JOIN	`JOIN ...`	全一致	JOIN型による

▶ サンプル: NOT EXISTSで注文のないユーザーを検索

SQL

SELECT u.user_id, u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.user_id
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

NOT EXISTSはNOT INより安全です。サブクエリ結果にNULLが含まれる場合、NOT INはクエリ全体で空の結果を返します。

▶ サンプル: NOT INのNULLの落とし穴

SQL

SELECT name FROM customers
WHERE region NOT IN ('NA', 'EU', NULL);

TEXT 📖 参照専用

(0 rows)

なぜならx NOT IN (a, b, NULL)はx <> a AND x <> b AND x <> NULLと同等で、x <> NULLはUNKNOWNであり、式全体がFALSEになるためです。

(3) ANY / ALL

▶ サンプル: ANYで部署内の誰よりも給与が高い従業員を検索

SQL

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary FROM employees WHERE department_id = 3
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

> MIN(サブクエリ結果)と同等です。

▶ サンプル: ALLで部署内の全員より給与が高い従業員を検索

SQL

SELECT name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department_id = 3
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

> MAX(サブクエリ結果)と同等です。

演算子	意味	同等の書き方
`> ANY (...)`	いずれかより大きい	`> MIN(...)`
`> ALL (...)`	すべてより大きい	`> MAX(...)`
`= ANY (...)`	いずれかと等しい	`IN (...)`

▶ サンプル: FROM内のテーブルサブクエリ

SQL

SELECT
  department_id,
  avg_salary,
  count
FROM (
  SELECT
    department_id,
    AVG(salary) AS avg_salary,
    COUNT(*)    AS count
  FROM employees
  GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 70000;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

4. 重要なポイント

(1) CTE（WITH句）

CTE（共通テーブル式）はWITHを使用して名前付きの一時結果セットを定義し、複数回参照できます。

▶ サンプル: CTEでネストされたサブクエリを簡略化

SQL

WITH regional_sales AS (
  SELECT
    region,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY region
),
top_regions AS (
  SELECT region
  FROM regional_sales
  WHERE total_sales > (SELECT AVG(total_sales) FROM regional_sales)
)
SELECT
  o.order_id,
  o.amount,
  o.region
FROM orders o
WHERE o.region IN (SELECT region FROM top_regions)
ORDER BY o.amount DESC;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

アプローチ	可読性	再利用可能	オプティマイザのインライン化	実体化
ネストサブクエリ	悪い	いいえ	はい	—
CTE	良い	はい	PG 12+で自動判▶	MATERIALIZEDを強制可能
一時テーブル	普通	はい	いいえ	ディスクに書き込み

▶ サンプル: CTE MATERIALIZEDで実体化を強制

SQL

WITH expensive_calc AS MATERIALIZED (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM expensive_calc
UNION ALL
SELECT * FROM expensive_calc;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

MATERIALIZEDは計算を1回だけ実行してキャッシュします。何度も参照され計算コストの高いCTEに最適です。

▶ サンプル: CTE NOT MATERIALIZEDでインライン化を強制

SQL

WITH simple_filter AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE region = 'NA'
)
SELECT * FROM simple_filter WHERE amount > 50000;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

NOT MATERIALIZEDはオプティマイザにインライン展開させます。単純な述語プッシュダウンシナリオに適しています。

(2) 再帰CTE（WITH RECURSIVE）

再帰CTEはツリーやグラフデータを扱う標準的なSQLアプローチです。

構文構造:

SQL

WITH RECURSIVE cte_name AS (
  base_query        -- アンカー: 非再帰のシード
  UNION ALL
  recursive_query   -- cte_name自身を参照
)
SELECT * FROM cte_name;

▶ サンプル: CEOからの全部下

SQL

WITH RECURSIVE subordinates AS (
  SELECT
    employee_id,
    name,
    manager_id,
    1 AS level,
    name::text AS path
  FROM employees
  WHERE manager_id IS NULL
    AND name = 'David'
  UNION ALL
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    s.level + 1,
    s.path || ' > ' || e.name
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT
  level,
  REPEAT('  ', level - 1) || name AS org_chart,
  path
FROM subordinates
ORDER BY path;

TEXT 📖 参照専用

 level |       org_chart        |              path
-------+------------------------+--------------------------------
     1 | David                  | David
     2 |   Alice                | David > Alice
     3 |     Bob                | David > Alice > Bob
     3 |     Charlie            | David > Alice > Charlie
     2 |   Eve                  | David > Eve
     3 |     Frank              | David > Eve > Frank

▶ サンプル: 再帰深度の制限（無限ループ防止）

SQL

WITH RECURSIVE subordinates AS (
  SELECT
    employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT
    e.employee_id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
  WHERE s.level < 5
)
SELECT * FROM subordinates;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

WHERE s.level < 5で再帰を最大5階層に制限します。

▶ サンプル: 日付系列の生成

SQL

WITH RECURSIVE date_series AS (
  SELECT '2025-01-01'::date AS dt
  UNION ALL
  SELECT dt + INTERVAL '1 day'
  FROM date_series
  WHERE dt < '2025-12-31'
)
SELECT dt FROM date_series;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. 実践

▶ サンプル: 各部��の最高給与従業員を検索

SQL

SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary = (
  SELECT MAX(salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: CTEで顧客RFMスコアを計算

SQL

WITH customer_orders AS (
  SELECT
    customer_id,
    MAX(created_at)                           AS last_order_date,
    COUNT(*)                                  AS frequency,
    SUM(amount)                               AS monetary
  FROM orders
  GROUP BY customer_id
)
SELECT
  customer_id,
  frequency,
  monetary,
  NTILE(4) OVER (ORDER BY monetary DESC) AS m_quartile
FROM customer_orders;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

▶ サンプル: 再帰CTEで商品カテゴリツリー

SQL

WITH RECURSIVE category_tree AS (
  SELECT
    category_id, parent_id, name, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT
    c.category_id, c.parent_id, c.name, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT
  depth,
  REPEAT('──', depth) || name AS tree_view
FROM category_tree
ORDER BY depth, name;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: EXISTSで全地域に注文された商品を検索

SQL

SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM regions r
  WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id
      AND o.region = r.region_code
  )
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: CTE + ウィンドウ関数でトップN

SQL

WITH ranked_orders AS (
  SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked_orders
WHERE rn <= 3;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

6. 総合サンプル

Aliceの組織図クエリ——任意のマネージャーから全部下をレベルインデント、パス、人数付きでリスト表示。

SQL

WITH RECURSIVE org_tree AS (
  SELECT
    employee_id,
    name,
    manager_id,
    1          AS level,
    name::text AS path,
    ARRAY[employee_id] AS subtree_ids
  FROM employees
  WHERE name = 'Alice'
  UNION ALL
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    o.level + 1,
    o.path || ' > ' || e.name,
    o.subtree_ids || e.employee_id
  FROM employees e
  JOIN org_tree o ON e.manager_id = o.employee_id
)
SELECT
  o.level,
  REPEAT('    ', o.level - 1) || o.name AS org_chart,
  o.path,
  ARRAY_LENGTH(o.subtree_ids, 1)       AS team_size
FROM org_tree o
ORDER BY o.path;

TEXT 📖 参照専用

 level |         org_chart          |              path               | team_size
-------+----------------------------+---------------------------------+-----------
     1 | Alice                      | Alice                           |         1
     2 |     Bob                    | Alice > Bob                     |         2
     3 |         Diana              | Alice > Bob > Diana             |         3
     3 |         Eve                | Alice > Bob > Eve               |         4
     2 |     Charlie                | Alice > Charlie                 |         5
     3 |         Frank              | Alice > Charlie > Frank         |         6

7. 再帰CTEの実行フロー

flowchart TD
    A["アンカークエリ<br/>（非再帰シード）"] --> B["作業テーブル T₀"]
    B --> C["再帰クエリ<br/>（T₀とJOIN）"]
    C --> D{"新しい行が<br/>生成された？"}
    D -->|はい| E["作業テーブル T₁"]
    E --> F["結果に追加"]
    F --> C
    D -->|いいえ| G["最終結果<br/>（全イテレーションのUNION ALL）"]

    style A fill:#e1f5fe
    style C fill:#fff9c4
    style G fill:#c8e6c9

ステップ	操作	説明
1	アンカークエリ実行	非再帰シード、初期行を生成
2	作業テーブルに格納	T₀ = アンカー結果
3	再帰クエリ	JOIN作業テーブルと元テーブル
4	新規行チェック	新規行があれば継続、なければ終了
5	結果マージ	UNION ALLですべてのイテレーション結果

❓ よくある質問

Q CTEとサブクエリにパフォーマンス差はありますか？

A PostgreSQL 12+はCTEをインライン化するか自動判断します。単純なCTEは通常インライン化され、複雑なものは実体化されます。MATERIALIZED/NOT MATERIALIZEDで手動制御してください。

Q 再帰CTEは無限ループする可能性がありますか？

A 可能性があります。データに循環（例: A→B→A）が含まれる場合、再帰は停止しません。レベル制限を追加するか、パスを追跡してノードの再訪を回避してください。

Q NOT INがNULLに当たると空を返すのはなぜですか？

A x NOT IN (a, NULL)はx<>a AND x<>NULLと同等で、x<>NULLはUNKNOWNです。AND連鎖ではUNKNOWNが行全体をFALSEにします。代わりにNOT EXISTSの方が安全です。

Q EXISTSとIN、どちらが速いですか？

A データとインデックスに依存します。通常、サブクエリ結果セットが小さい場合はINが速く、外部テーブルが小さい場合はEXISTSが速くなります。PostgreSQLオプティマイザが自動的に書き換えるため、多くの場合パフォーマンスは近くなります。

Q 再帰CTEはグラフ構造を扱えますか？

A はい。ただし循環防止ロジックが追加で必要です。再帰部分で訪問済みノードを（ARRAYやパス文字列で）追跡し、再訪を回避してください。

Q CTEは前に定義されたCTEを参照できますか？

A はい。WITH a AS (...), b AS (SELECT ... FROM a)では、bはaを参照できます。定義順に下方向に参照します。

📖 まとめ

サブクエリにはスカラー/カラム/行/テーブルの4種類があり、それぞれ適切な場所があります
EXISTS/NOT EXISTSはIN/NOT INより安全で、NULLの影響を受けません
ANYは「最小値より大きい」、ALLは「最大値より大きい」と同等です
CTEはWITHで名前付き一時結果を定義し、ネストサブクエリよりはるかに読みやすいです
PostgreSQL 12+はCTEのインライン化/実体化を自動判断します。手動制御も可能です
WITH RECURSIVEはツリー/グラフデータを扱い、標準的なSQLアプローチです
再帰CTEはアンカー＋再帰部分が必要で、UNION ALLで結合します
無限再帰を防ぐにはレベル制限や追跡パスを使用してください

📝 練習問題

⭐ スカラーサブクエリを使用して、給与が会社の平均を上回る従業員を検索してください。
⭐ NOT EXISTSを使用して、一度も注文したことのない顧客を検索してください。
⭐⭐ 次のネストサブクエリをCTEで書き直してください。各顧客の平均注文金額を上回る注文を検索します。
⭐⭐ WITH RECURSIVEを使用して、employeesテーブルから指定されたマネージャーの3階層分の部下をレベルインデント付きでクエリしてください。
⭐⭐⭐ 再帰CTEと集計の組み合わせ: CEOから始めて、各マネージャーの直属の部下数とサブツリー全体の人数を1つのSQL文でカウントしてください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて