レッスン2：JOIN、サブクエリ、CTEによる高度なクエリ

このレッスンのロックを解除するには、まず「レッスン1: PostgreSQLの基礎とリレーショナル設計」を完了してください。前のレッスンに戻る

はじめに

実際のバックエンドプロジェクトでPostgreSQLを扱う際、単一のテーブルだけをクエリすることはほとんどありません。データは外部キーによって紐づく複数のテーブルに正規化されていることが多く、それらを組み合わせて意味のある情報を取得する必要があります。このレッスンでは、複雑なクエリを明確かつ効率的に書くための3つの重要なテクニック——テーブルを結合するJOIN、ネストしたクエリを書くサブクエリ、そしてクエリロジックを構造化するCTE——を中心に学びます。

本章では、販売システムを模擬したサンプルスキーマを用います。customers、orders、products、order_itemsの4つの主要テーブルで構成されています。customersテーブルは顧客情報を、ordersは顧客の注文を、productsは商品カタログを保持し、order_itemsは各注文に含まれる各商品の詳細を格納する中間テーブルです。これは、実際の開発現場で頻繁に遭遇する、一対多が入れ子になった関係性の典型例です。

複数テーブルにまたがるJOIN

JOINは、同一クエリ内で複数テーブルのデータを組み合わせる最も一般的な方法です。PostgreSQLはINNER JOIN（両方のテーブルにマッチするレコードのみ取得）、LEFT JOIN（右側のマッチがなくても左側の全レコードを取得）、およびRIGHT JOIN、FULL OUTER JOINなどのバリエーションをサポートしています。バックエンドの実務で最もよく使われるのはINNER JOINとLEFT JOINです。

以下の例では、注文一覧に顧客名と合計金額を含めて取得します：

sql

SELECT o.id AS order_id, c.name AS customer_name, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 10;

複数のテーブルを同時にJOINするには、JOIN句を追加していくだけです。以下の例では、注文内の各商品の商品名と数量を含む詳細を取得しています：

sql

SELECT o.id AS order_id, p.name AS product_name, oi.quantity, oi.unit_price
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 42;

よくある間違いは、ON条件を書き忘れてJOINしてしまい、デカルト積が発生してクエリが非常に遅くなることです。すべてのJOINに明確なキー一致条件があることを必ず確認してください。

customers、orders、order_items、productsの4テーブル間の関係図。外部キーのリレーションが線で結ばれている

サブクエリと相関サブクエリ

サブクエリとは、別のクエリの内側にネストされたSELECTクエリです。WHERE句でのフィルタリング、SELECTでの値計算、FROMでの一時テーブル作成など、さまざまな場所で使用できます。特殊な形式として相関サブクエリがあり、これは内側のクエリが外側のクエリのカラムを参照するため、結果行ごとに再実行されます。

少なくとも1件の注文を持つ顧客を単純なサブクエリで見つける例：

sql

SELECT name, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total_amount > 500);

表示される各顧客の注文数を相関サブクエリでカウントする例：

sql

SELECT c.name, c.email,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c
WHERE c.created_at > '2024-01-01';

内側のクエリが外側のc.idを参照しているため、PostgreSQLは顧客ごとにそれを再実行しなければなりません。テーブルが大きい場合、相関サブクエリは遅くなる可能性があるため、パフォーマンスが問題になる場合はJOINやCTEへの切り替えを検討してください。

WITH句による共通テーブル式（CTE）

CTE（Common Table Expression）を使用すると、文内で名前付きの一時的なクエリを定義でき、複雑なロジックが読みやすくなります。CTEはキーワードWITHで始まり、その後に名前を付けたクエリのリストが続き、最後にこれらを利用するメインクエリが続きます。

2024年の各顧客の総売上を計算し、売上が1000を超える顧客のみをフィルタリングする例：

sql

WITH customer_revenue AS (
  SELECT o.customer_id, SUM(o.total_amount) AS revenue
  FROM orders o
  WHERE o.created_at >= '2024-01-01' AND o.created_at < '2025-01-01'
  GROUP BY o.customer_id
)
SELECT c.name, cr.revenue
FROM customer_revenue cr
INNER JOIN customers c ON c.id = cr.customer_id
WHERE cr.revenue > 1000
ORDER BY cr.revenue DESC;

CTEには再帰形式（再帰CTE）もあり、WITH RECURSIVEキーワードを使用します。会社の組織構造や多階層の商品カテゴリなど、階層データの走査に非常に便利です。また、PostgreSQL 12以降では、AS MATERIALIZED句でマテリアライズドを指定でき、特定ケースでクエリの高速化が可能です。

🖼️ [ここに画像挿入のヒント：CTEクエリの4ステップのフロー図（一時テーブルの宣言から結果のフィルタリングとソートまで）

よくある間違いと実践的なTips

よくある間違いは、WHERE句でINやEXISTSを使ったサブクエリとJOINを取り違えることです。多くの場合、両者は同等の結果を返しますが、値を取得する代わりに存在チェックだけが必要なときは、EXISTSの方が通常高速です。注文したことのある顧客を探す例：

sql

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

CTEを書く際は、customer_revenueやtop_productsのような意味のある名前を付け、各ステップの目的を読者が理解しやすくしましょう。単純なJOINで十分な場合にCTEを濫用するのは避けてください。一部のPostgreSQLバージョンではCTEに追加の最適化コストがかかることがあります。最後に、複雑なクエリが遅いと感じたときは、必ずEXPLAIN ANALYZEで実行計画を確認してください。相関サブクエリをJOINに置き換えるだけで、大幅にスピードアップできることがあります。

まとめ

このレッスンでは、JOINで複数テーブルを結合する方法、サブクエリと相関サブクエリの書き方、および複雑なクエリを読みやすいブロックに構造化するCTE（WITH）の使い方を学びました。これら3つのテクニックは、注文詳細の取得から集計レポートの作成まで、実務のバックエンドアプリで発生するほとんどのクエリ要件を扱うための基礎となります。次のレッスンでは、ここで書いた複雑なクエリのインデックスとパフォーマンス最適化について学びます。