015 サブクエリその2 | サブクエリをJOIN

サブクエリをJOINして用いる

1. サブクエリとJOINの基本

データベースのクエリを最適化し、複雑なデータの取得を行うために、サブクエリをJOINして使用する手法を学びます。サブクエリをJOINすることで、パフォーマンスを向上させ、コードの可読性を高めることができます。

サブクエリとJOINの組み合わせは実務でも頻繁に使われる重要なスキルです。しっかりと理解し、使いこなせるようにしておきましょう。繰り返し学習し、実際にクエリを書いて試すことで、確実に身につけていきましょう！

2. 見本のテーブル構成

以下の3つのテーブルを使用します。

orders（注文情報）

order_id	customer_id	order_date	total_amount
1	101	2024-02-01	5000
2	102	2024-02-02	7000
3	101	2024-02-05	3000

customers（顧客情報）

customer_id	name	region
101	田中 太郎	関東
102	山田 花子	関西
103	鈴木 次郎	東北

regions（地域情報）

region_id	region_name
1	関東
2	関西
3	東北

3. サブクエリをJOINして使用する例

3-1. 顧客の注文合計金額を取得する

複雑なクエリに圧倒されてしまうでしょうか？ でも安心してください。内側のSQL（サブクエリ）から考えていけば簡単です。

内側のクエリでは、まず ordersテーブルの customer_idごとに SUM(total_amount)を集計しています。

外側のSQLは、内側の集計結果をJOINすることで、顧客ごとの合計注文金額を取得しています。

このように内側のSQLから順に考えていくと、 ordersテーブルのデータがどのように集計され、最終的に customersテーブルと結合されるのかが理解しやすくなります。

SELECT c.customer_id, c.name, total_orders.total_amount
FROM customers c
JOIN (
    SELECT customer_id, SUM(total_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) total_orders
ON c.customer_id = total_orders.customer_id;

クエリ結果

customer_id	name	total_amount
101	田中 太郎	8000
102	山田 花子	7000

3-2. 地域ごとの売上を取得する

SELECT r.region_name, region_sales.total_sales
FROM regions r
JOIN (
    SELECT c.region, SUM(o.total_amount) AS total_sales
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.region
) region_sales
ON r.region_name = region_sales.region;

このクエリでは、 customersと ordersをJOINして地域ごとの売上を計算し、その結果を regionsテーブルとJOINすることで、各地域の売上を取得しています。

クエリ結果

region_name	total_sales
関東	8000
関西	7000

4. 複数のサブクエリをJOINする多重構造の解読方法

4-1. 複雑なクエリの例

SELECT c.name, r.region_name, customer_orders.total_orders, region_sales.total_sales
FROM customers c
JOIN (
    SELECT customer_id, COUNT(order_id) AS total_orders
    FROM orders
    GROUP BY customer_id
) customer_orders
ON c.customer_id = customer_orders.customer_id
JOIN (
    SELECT c.region, SUM(o.total_amount) AS total_sales
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.region
) region_sales
ON c.region = region_sales.region;

このクエリでは、

1. customer_ordersサブクエリで顧客ごとの注文数を取得。
2. region_salesサブクエリで地域ごとの売上を計算。
3. これらを customersテーブルとJOINして、顧客の名前・地域・注文数・地域の売上を取得。

クエリ結果

name	region_name	total_orders	total_sales
田中 太郎	関東	2	8000
山田 花子	関西	1	7000

4-2. 解読のポイント

• サブクエリごとに意味を理解する
• customer_ordersは「顧客ごとの注文数」
• region_salesは「地域ごとの売上」
• JOINの関係を確認する
• customers→ customer_ordersは customer_idで結合
• customers→ region_salesは regionで結合
• 集約関数の適用範囲を意識する
• SUM(total_amount)や COUNT(order_id)が適切なレベルで計算されているか確認

5. まとめ

サブクエリをJOINすることで、より柔軟かつ効率的にデータを取得できます。ただし、

• クエリの構造を分解して考える
• 集約関数の適用範囲を把握する
• JOINの関係を明確にする といったポイントを意識すると、複雑なクエリも理解しやすくなります。

---

6. 練習問題

以下の練習問題に挑戦し、学んだ内容を定着させましょう。

問題1: 各顧客の最新の注文を取得するクエリを作成せよ

order_id	customer_id	order_date	total_amount
3	101	2024-02-05	3000
2	102	2024-02-02	7000

問題2: 各地域の平均注文金額を取得するクエリを作成せよ

region_name	avg_total_amount
関東	4000
関西	7000

問題3: 注文数が2件以上の顧客を取得するクエリを作成せよ

customer_id	name	order_count
101	田中 太郎	2

---

解答を考えたら、実際にSQLを実行して正しい結果が得られるか確認してみましょう！

1. ホーム
2. プログラミングの覚書
3. レッスンテキストの目次