🔗 Step 10: CROSS JOINと自己結合

特殊な結合パターンをマスターしよう!

📋 このステップで学ぶこと

CROSS JOIN（直積結合）とは何か
CROSS JOINの用途と使い方
自己結合（Self Join）のパターン
階層構造（組織図、カテゴリツリー）の扱い方
組み合わせの全列挙
欠損データの補完

🎯 1. CROSS JOINとは?

1-1. すべての組み合わせを作る

これまで学んだJOIN（INNER JOIN、LEFT JOIN、RIGHT JOIN）は、結合条件を指定して、条件に合う行同士を結合しました。

CROSS JOINは、結合条件を指定せずに、2つのテーブルのすべての組み合わせを作ります。数学では「直積」や「デカルト積」と呼ばれます。

💡 CROSS JOINのイメージ（トランプの例）

トランプの「スート（マーク）」と「数字」を組み合わせる場合を考えてみましょう。

スート	数字	組み合わせ
♠ ♥ ♦ ♣	1〜13	4 × 13 = 52枚

4つのスート × 13の数字 = 52通りの組み合わせ（52枚のカード）が作られます。CROSS JOINはこのような「全組み合わせ」を作る結合です。

1-2. CROSS JOINの視覚的な理解

2つの小さなテーブルでCROSS JOINの動作を見てみましょう。

テーブルA（3行）:

id	name
1	りんご
2	バナナ
3	みかん

テーブルB（2行）:

size
S
L

CROSS JOIN結果（3 × 2 = 6行）:

id	name	size
1	りんご	S
1	りんご	L
2	バナナ	S
2	バナナ	L
3	みかん	S
3	みかん	L

各商品がすべてのサイズと組み合わされています。

📌 CROSS JOINの結果行数

結果の行数 = テーブル1の行数 × テーブル2の行数

例: 100行 × 100行 = 10,000行
例: 1,000行 × 1,000行 = 1,000,000行（100万行!）

大きなテーブル同士のCROSS JOINは膨大な結果になるので注意が必要です。

📏 2. CROSS JOINの基本構文

2-1. 書き方は2通り

📌 CROSS JOINの構文

方法1: CROSS JOIN キーワードを使う（推奨）

※横にスクロールできます

SELECT *
FROM テーブル1
CROSS JOIN テーブル2;

方法2: カンマで区切る（古い書き方）

※横にスクロールできます

SELECT *
FROM テーブル1, テーブル2;

どちらも同じ結果ですが、CROSS JOINと明示的に書く方が意図が分かりやすいです。

2-2. 例1：商品とサイズの組み合わせ

やりたいこと：3つの商品と4つのサイズの全組み合わせを作る

まず、商品とサイズのデータを作ります（サブクエリで作成）。

※横にスクロールできます

SELECT 
    p.product_name,
    s.size_name
FROM (
    SELECT ‘Tシャツ’ AS product_name UNION ALL
    SELECT ‘パーカー’ UNION ALL
    SELECT ‘ジーンズ’
) p
CROSS JOIN (
    SELECT ‘S’ AS size_name UNION ALL
    SELECT ‘M’ UNION ALL
    SELECT ‘L’ UNION ALL
    SELECT ‘XL’
) s
ORDER BY p.product_name, s.size_name;

クエリの解説:

UNION ALL → 複数の行を縦に結合（テストデータの作成）
p、s → サブクエリに別名をつける
CROSS JOIN → 全組み合わせを作成
結果: 3商品 × 4サイズ = 12行

結果イメージ:

product_name	size_name
Tシャツ	L
Tシャツ	M
Tシャツ	S
Tシャツ	XL
ジーンズ	L
…	（計12行）

2-3. 例2：カレンダーの作成

やりたいこと：2024年と2025年の全月（24ヶ月分）のリストを作る

※横にスクロールできます

SELECT 
    y.year,
    m.month,
    y.year || ‘-‘ || printf(‘%02d’, m.month) AS year_month
FROM (
    SELECT 2024 AS year UNION ALL
    SELECT 2025
) y
CROSS JOIN (
    SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) m
ORDER BY y.year, m.month;

クエリの解説:

y → 年のテーブル（2行: 2024, 2025）
m → 月のテーブル（12行: 1〜12）
printf('%02d', m.month) → 月を2桁でフォーマット（01, 02…）
|| → 文字列の連結
結果: 2年 × 12ヶ月 = 24行

活用シーン:

レポート用カレンダー：売上がない月も含めて全月表示
日付マスター作成：1年分の日付リストを生成
時間帯マスター作成：24時間 × 60分 = 1440行の時刻表

🔧 3. CROSS JOINの実用例

3-1. 欠損データの補完

CROSS JOINの最も重要な用途は、欠損データの補完です。売上がない日や商品があっても、全ての日付×商品の組み合わせを表示したい場合に使います。

やりたいこと：過去7日間の各商品の売上を表示する（売上がない日は0）

コードを段階的に見ていきましょう。

ステップ1：7日分の日付リストを作る

※横にスクロールできます

WITH date_range AS (
    SELECT DATE(‘now’, ‘-‘ || (n – 1) || ‘ days’) AS date
    FROM (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    )
)

解説:

DATE('now', '-0 days') → 今日
DATE('now', '-1 days') → 昨日
DATE('now', '-6 days') → 6日前
結果: 過去7日間の日付リスト

ステップ2：日付 × 商品の全組み合わせを作る

※横にスクロールできます

all_combinations AS (
    SELECT 
        d.date,
        p.product_id,
        p.product_name
    FROM date_range d
    CROSS JOIN products p
)

ステップ3：実際の売上データをLEFT JOINで結合

※横にスクロールできます

SELECT 
    ac.date,
    ac.product_name,
    COALESCE(SUM(o.total_amount), 0) AS sales
FROM all_combinations ac
LEFT JOIN orders o 
    ON DATE(o.order_date) = ac.date 
    AND o.product_id = ac.product_id
GROUP BY ac.date, ac.product_id, ac.product_name

解説:

LEFT JOIN → 全組み合わせを維持しつつ、売上データを結合
COALESCE(..., 0) → NULLの場合は0に置き換え

完成コード:

※横にスクロールできます

WITH date_range AS (
    SELECT DATE(‘now’, ‘-‘ || (n – 1) || ‘ days’) AS date
    FROM (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    )
),
all_combinations AS (
    SELECT 
        d.date,
        p.product_id,
        p.product_name
    FROM date_range d
    CROSS JOIN products p
)
SELECT 
    ac.date AS 日付,
    ac.product_name AS 商品名,
    COALESCE(SUM(o.total_amount), 0) AS 売上
FROM all_combinations ac
LEFT JOIN orders o 
    ON DATE(o.order_date) = ac.date 
    AND o.product_id = ac.product_id
GROUP BY ac.date, ac.product_id, ac.product_name
ORDER BY ac.date DESC, ac.product_name
LIMIT 20;

結果イメージ:

日付	商品名	売上
2024-12-01	りんご	5,000
2024-12-01	バナナ	0（売上なし）
2024-12-01	みかん	3,000
2024-11-30	りんご	0（売上なし）

売上がない日・商品も0として表示されます。

💡 この手法が役立つ場面

売上レポート：0円の日も含めて全日表示
在庫管理：全商品×全倉庫のマトリックス表示
アクセス解析：アクセスがない時間帯も0件で表示
グラフ作成：データの欠損なくグラフを描画

⚠️ CROSS JOINの注意点

CROSS JOINは結果が膨大になる可能性があります。

必ず以下を確認してください:
• 結合するテーブルの行数を事前に確認
• 結果行数（行数 × 行数）が許容範囲か確認
• 必要に応じてWHERE句で絞り込み
• LIMITを使ってテスト

🔄 4. 自己結合（Self Join）とは?

4-1. 同じテーブルを自分自身と結合する

自己結合（Self Join）は、同じテーブルを2回使って結合することです。1つのテーブルを「別の2つのテーブル」として扱い、それらを結合します。

💡 自己結合のイメージ（鏡の例）

1つのテーブルを「コピー」して、元のテーブルとコピーを結合するイメージです。

元テーブル（t1）	コピー（t2）	結合結果
社員テーブル	同じ社員テーブル	社員と上司の関係
商品テーブル	同じ商品テーブル	商品同士の比較

実際にはテーブルをコピーするわけではなく、別名（エイリアス）を使って区別します。

4-2. なぜ自己結合が必要なのか

自己結合が必要になる典型的なケースを見てみましょう。

社員テーブル（employees）:

employee_id	employee_name	manager_id
1	佐藤部長	NULL（トップ）
2	山田課長	1（佐藤部長）
3	鈴木主任	2（山田課長）
4	田中	3（鈴木主任）

manager_idは「上司のemployee_id」を指しています。田中さん（ID:4）の上司は鈴木主任（ID:3）です。

このテーブルで「社員名と上司名」を表示したい場合、同じテーブルを2回参照する必要があります。

1回目: 社員の情報を取得
2回目: 上司の情報（名前）を取得

これを実現するのが自己結合です。

4-3. 自己結合の基本構文

📌 自己結合の書き方

※横にスクロールできます

SELECT 
    t1.列名 AS 別名1,
    t2.列名 AS 別名2
FROM テーブル AS t1
JOIN テーブル AS t2 ON t1.関連列 = t2.キー列;

ポイント:

AS t1、AS t2 → 同じテーブルに別名をつけて区別
別名は必須（つけないとエラーになる）
t1.列名、t2.列名 → どちらのテーブルの列か明示

👥 5. 自己結合の基本例

5-1. 例1：社員と上司の関係を表示

やりたいこと：各社員の名前と、その上司の名前を表示する

※横にスクロールできます

SELECT 
    e1.employee_name AS 社員名,
    e2.employee_name AS 上司名
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id
ORDER BY e1.employee_name;

クエリの解説:

e1 → 社員側（自分自身の情報）
e2 → 上司側（上司の情報）
e1.manager_id = e2.employee_id → 社員の上司ID = 上司のID
LEFT JOIN → 上司がいない社員も表示（トップの社員）

なぜLEFT JOIN?:

INNER JOINだと、上司がいない（manager_idがNULL）社員が表示されません。全社員を表示するためにLEFT JOINを使います。

結果イメージ:

社員名	上司名
佐藤部長	NULL（トップなので上司なし）
山田課長	佐藤部長
鈴木主任	山田課長
田中	鈴木主任

5-2. 結合の流れを視覚的に理解する

📌 自己結合の処理イメージ

田中さん（employee_id: 4, manager_id: 3）の場合:

ステップ	処理	結果
1	e1から田中さんの行を取得	e1.manager_id = 3
2	e2からemployee_id = 3の行を探す	e2 = 鈴木主任
3	結合結果を出力	田中 \| 鈴木主任

5-3. 例2：同じカテゴリの商品を比較する

やりたいこと：同じカテゴリ内で、より高い価格の商品を見つける

※横にスクロールできます

SELECT 
    p1.product_name AS 商品1,
    p1.price AS 価格1,
    p2.product_name AS 商品2,
    p2.price AS 価格2,
    p1.category AS カテゴリ
FROM products p1
JOIN products p2 
    ON p1.category = p2.category 
    AND p1.price < p2.price
ORDER BY p1.category, p1.product_name
LIMIT 10;

クエリの解説:

p1.category = p2.category → 同じカテゴリの商品同士を結合
p1.price < p2.price → p1より高いp2を見つける
結果: 「この商品より高い商品はこれ」というペアのリスト

結果イメージ:

商品1	価格1	商品2	価格2	カテゴリ
バナナ	120	りんご	150	果物
バナナ	120	ぶどう	500	果物
りんご	150	ぶどう	500	果物

バナナ（120円）より高い果物は「りんご」と「ぶどう」の2つあります。

🔢 6. 重複を避ける方法

6-1. 問題：同じペアが2回出てくる

自己結合で「商品のペア」を作ると、同じペアが2回出てくることがあります。

問題のあるクエリ:

※横にスクロールできます

-- 重複が発生する
SELECT 
    p1.product_name AS 商品1,
    p2.product_name AS 商品2
FROM products p1
JOIN products p2 
    ON p1.category = p2.category
    AND p1.product_id != p2.product_id;  -- 自分自身は除外

結果:

商品1	商品2
りんご	バナナ
バナナ	りんご

「りんご-バナナ」と「バナナ-りんご」は実質同じペアなのに、両方出てきます。

6-2. 解決策：ID の大小で一方向のみにする

✅ 重複を避けるクエリ:

※横にスクロールできます

SELECT 
    p1.product_name AS 商品1,
    p2.product_name AS 商品2
FROM products p1
JOIN products p2 
    ON p1.category = p2.category
    AND p1.product_id < p2.product_id;  -- 小さいID → 大きいID のみ

結果:

商品1	商品2
りんご	バナナ

重複なく1つのペアだけが表示されます!

なぜ < を使うのか:

!= (等しくない) → 両方向のペアが出る（A-B、B-A）
< (より小さい) → 一方向のみ（A-B だけ）
> (より大きい) → 一方向のみ（B-A だけ）

< または > を使うことで、重複なくユニークなペアだけを取得できます。

6-3. 例：同じ都道府県の顧客ペア

やりたいこと：同じ都道府県に住む顧客のペアを表示する（重複なし）

※横にスクロールできます

SELECT 
    c1.customer_name AS 顧客1,
    c2.customer_name AS 顧客2,
    c1.prefecture AS 都道府県
FROM customers c1
JOIN customers c2 
    ON c1.prefecture = c2.prefecture 
    AND c1.customer_id < c2.customer_id
ORDER BY c1.prefecture, c1.customer_name
LIMIT 20;

クエリの解説:

c1.prefecture = c2.prefecture → 同じ都道府県
c1.customer_id < c2.customer_id → 重複を避ける

活用シーン:

顧客マッチング：同じ属性を持つ顧客の発見
重複チェック：同じメールアドレスの顧客を探す
類似商品：同じカテゴリ・価格帯の商品ペア

🌳 7. 階層構造の扱い

7-1. 階層構造とは

階層構造（ツリー構造）とは、親子関係を持つデータ構造のことです。実務では頻繁に登場します。

階層構造の例:

組織図：部長 → 課長 → 係長 → 一般社員
カテゴリ：食品 → 果物 → りんご
フォルダ：ドキュメント → 仕事 → プロジェクトA
コメント：投稿 → コメント → 返信 → 返信への返信

これらの階層構造は、自己参照のテーブル設計で表現されます。各行が「親のID」を持ち、同じテーブル内の別の行を参照します。

7-2. 2階層の組織図を表示

やりたいこと：社員、上司、上司の上司（3階層）を表示する

※横にスクロールできます

SELECT 
    e1.employee_name AS 社員,
    e2.employee_name AS 上司,
    e3.employee_name AS 上司の上司
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id
LEFT JOIN employees e3 ON e2.manager_id = e3.employee_id
ORDER BY e1.employee_name;

クエリの解説:

e1 → 社員本人
e2 → 社員の上司（e1.manager_id → e2.employee_id）
e3 → 上司の上司（e2.manager_id → e3.employee_id）
LEFT JOINを2回使って3階層を表現

結果イメージ:

社員	上司	上司の上司
佐藤部長	NULL	NULL
山田課長	佐藤部長	NULL
鈴木主任	山田課長	佐藤部長
田中	鈴木主任	山田課長

7-3. カテゴリの親子関係

やりたいこと：カテゴリと親カテゴリの名前を表示する

カテゴリテーブル（categories）の構造:

category_id	category_name	parent_id
1	食品	NULL（ルート）
2	果物	1（食品）
3	野菜	1（食品）
4	りんご	2（果物）
5	キャベツ	3（野菜）

※横にスクロールできます

SELECT 
    c1.category_name AS カテゴリ,
    c2.category_name AS 親カテゴリ
FROM categories c1
LEFT JOIN categories c2 ON c1.parent_id = c2.category_id
ORDER BY c1.category_id;

結果イメージ:

カテゴリ	親カテゴリ
食品	NULL
果物	食品
野菜	食品
りんご	果物
キャベツ	野菜

7-4. パス（フルパス）の生成

やりたいこと：「食品 > 果物 > りんご」のようなフルパスを作る

※横にスクロールできます

SELECT 
    c1.category_name,
    COALESCE(c3.category_name || ' > ', '') || 
    COALESCE(c2.category_name || ' > ', '') || 
    c1.category_name AS full_path
FROM categories c1
LEFT JOIN categories c2 ON c1.parent_id = c2.category_id
LEFT JOIN categories c3 ON c2.parent_id = c3.category_id
ORDER BY c1.category_id;

クエリの解説:

c1 → 対象のカテゴリ
c2 → 親カテゴリ
c3 → 祖父カテゴリ（親の親）
COALESCE(..., '') → NULLの場合は空文字に
|| → 文字列の連結

結果イメージ:

category_name	full_path
食品	食品
果物	食品 > 果物
りんご	食品 > 果物 > りんご

📌 階層が深い場合の対処

自己結合を繰り返すと、階層の数だけJOINが必要になります。階層が深い場合（5階層以上など）は、再帰クエリ（WITH RECURSIVE）を使う方が効率的です。再帰クエリについては別のステップで学びます。

🎲 8. 組み合わせの列挙

8-1. 商品のセット（2つの組み合わせ）

やりたいこと：異なる2つの商品の組み合わせと、セット価格を計算する

※横にスクロールできます

SELECT 
    p1.product_name AS 商品1,
    p1.price AS 価格1,
    p2.product_name AS 商品2,
    p2.price AS 価格2,
    p1.price + p2.price AS セット価格
FROM products p1
CROSS JOIN products p2
WHERE p1.product_id < p2.product_id
ORDER BY セット価格 DESC
LIMIT 10;

クエリの解説:

CROSS JOIN → 全商品の組み合わせを作成
p1.product_id < p2.product_id → 重複を避ける & 自分自身を除外
p1.price + p2.price → 2つの商品の合計価格

結果イメージ（セット価格の高い順）:

商品1	価格1	商品2	価格2	セット価格
ぶどう	500	いちご	800	1,300
ぶどう	500	メロン	1,000	1,500

8-2. 数字の組み合わせ

やりたいこと：1から5までの数字で、異なる2つの数字の組み合わせを作る

※横にスクロールできます

SELECT 
    n1.num AS 数字1,
    n2.num AS 数字2,
    n1.num + n2.num AS 合計,
    n1.num * n2.num AS 積
FROM (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 
    UNION ALL SELECT 4 UNION ALL SELECT 5
) n1
CROSS JOIN (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 
    UNION ALL SELECT 4 UNION ALL SELECT 5
) n2
WHERE n1.num < n2.num
ORDER BY n1.num, n2.num;

結果イメージ:

数字1	数字2	合計	積
1	2	3	2
1	3	4	3
1	4	5	4
...	...	...	（計10組）

5個から2個選ぶ組み合わせは 5C2 = 10通りです。

💼 9. 実務での活用例

9-1. 共通購入商品の分析（協調フィルタリングの基礎）

やりたいこと：同じ商品を買った顧客のペアと、共通商品数を計算する

これは「この商品を買った人はこの商品も買っています」という推薦システムの基礎になります。

※横にスクロールできます

SELECT 
    c1.customer_name AS 顧客1,
    c2.customer_name AS 顧客2,
    COUNT(DISTINCT o1.product_id) AS 共通商品数
FROM orders o1
JOIN orders o2 
    ON o1.product_id = o2.product_id
    AND o1.customer_id < o2.customer_id
JOIN customers c1 ON o1.customer_id = c1.customer_id
JOIN customers c2 ON o2.customer_id = c2.customer_id
GROUP BY c1.customer_id, c2.customer_id, c1.customer_name, c2.customer_name
HAVING COUNT(DISTINCT o1.product_id) >= 2
ORDER BY 共通商品数 DESC
LIMIT 10;

クエリの解説:

o1.product_id = o2.product_id → 同じ商品を買った注文を結合
o1.customer_id < o2.customer_id → 顧客ペアの重複を避ける
COUNT(DISTINCT o1.product_id) → 共通して買った商品の数
HAVING ... >= 2 → 共通商品が2つ以上のペアだけ表示

結果イメージ:

顧客1	顧客2	共通商品数
山田太郎	鈴木花子	5
田中次郎	佐藤美咲	4

共通商品が多い顧客は、購買傾向が似ている可能性があります。

9-2. 月次ランキングの比較

やりたいこと：今月と先月の売上ランキングを比較し、順位変動を計算する

※横にスクロールできます

WITH monthly_ranking AS (
    SELECT 
        strftime('%Y-%m', order_date) AS month,
        product_id,
        SUM(total_amount) AS sales,
        RANK() OVER (
            PARTITION BY strftime('%Y-%m', order_date) 
            ORDER BY SUM(total_amount) DESC
        ) AS ranking
    FROM orders
    GROUP BY strftime('%Y-%m', order_date), product_id
)
SELECT 
    p.product_name AS 商品名,
    m1.sales AS 今月売上,
    m1.ranking AS 今月ランク,
    m2.sales AS 先月売上,
    m2.ranking AS 先月ランク,
    COALESCE(m2.ranking, 0) - COALESCE(m1.ranking, 0) AS ランク変動
FROM products p
LEFT JOIN monthly_ranking m1 
    ON p.product_id = m1.product_id 
    AND m1.month = '2024-02'
LEFT JOIN monthly_ranking m2 
    ON p.product_id = m2.product_id 
    AND m2.month = '2024-01'
WHERE m1.ranking IS NOT NULL OR m2.ranking IS NOT NULL
ORDER BY m1.ranking;

クエリの解説:

monthly_ranking → CTEで月ごとのランキングを計算
m1 → 今月のランキングデータ
m2 → 先月のランキングデータ
m2.ranking - m1.ranking → 順位変動（プラス = 上昇、マイナス = 下降）

同じテーブル（CTE）を2回使用:

monthly_rankingを「今月用（m1）」と「先月用（m2）」として2回使い、自己結合のように比較しています。

9-3. スケジュール表の作成

やりたいこと：全社員 × 全会議室 × 全時間帯のスケジュール表を作る

※横にスクロールできます

SELECT 
    e.employee_name AS 社員,
    r.room_name AS 会議室,
    t.time_slot AS 時間帯
FROM employees e
CROSS JOIN (
    SELECT '会議室A' AS room_name UNION ALL
    SELECT '会議室B' UNION ALL
    SELECT '会議室C'
) r
CROSS JOIN (
    SELECT '09:00-10:00' AS time_slot UNION ALL
    SELECT '10:00-11:00' UNION ALL
    SELECT '11:00-12:00' UNION ALL
    SELECT '13:00-14:00' UNION ALL
    SELECT '14:00-15:00'
) t
ORDER BY e.employee_name, t.time_slot, r.room_name
LIMIT 30;

クエリの解説:

3つのテーブルをCROSS JOINで結合
結果: 社員数 × 会議室数 × 時間帯数の組み合わせ
例: 10人 × 3室 × 5枠 = 150行

活用シーン:

会議室予約システム：空き状況の一覧表示
シフト管理：全スタッフ × 全日付 × 全時間帯
テストケース生成：入力値の全組み合わせ

📝 練習問題

CROSS JOINと自己結合をマスターしましょう!

問題 1基本

CROSS JOINの基本

商品テーブルとカラーテーブルをCROSS JOINして、全ての組み合わせを表示してください。
カラーは「赤」「青」「緑」の3色とします。

※横にスクロールできます

SELECT 
    p.product_name AS 商品名,
    c.color AS カラー
FROM products p
CROSS JOIN (
    SELECT '赤' AS color UNION ALL
    SELECT '青' UNION ALL
    SELECT '緑'
) c
ORDER BY p.product_name, c.color
LIMIT 15;

解説:

CROSS JOINで全商品 × 全カラーの組み合わせを作成
商品数 × 3色 = 結果行数

問題 2基本

自己結合の基本

商品テーブルを自己結合して、価格が同じ商品のペアを表示してください。（同じ商品同士は除外）

※横にスクロールできます

SELECT 
    p1.product_name AS 商品1,
    p2.product_name AS 商品2,
    p1.price AS 価格
FROM products p1
JOIN products p2 
    ON p1.price = p2.price
    AND p1.product_id < p2.product_id
ORDER BY p1.price, p1.product_name;

解説:

p1.price = p2.price → 同じ価格の商品を結合
p1.product_id < p2.product_id → 重複を避ける

問題 3基本

カテゴリが同じ商品

同じカテゴリに属する商品のペアを表示してください。（自分自身は除外、重複なし）

※横にスクロールできます

SELECT 
    p1.product_name AS 商品1,
    p2.product_name AS 商品2,
    p1.category AS カテゴリ
FROM products p1
JOIN products p2 
    ON p1.category = p2.category
    AND p1.product_id < p2.product_id
ORDER BY p1.category, p1.product_name
LIMIT 20;

解説:

同じカテゴリで結合
< で重複を避ける

問題 4応用

価格差の計算

同じカテゴリ内で、価格が高い商品と安い商品のペアを作り、価格差を表示してください。

※横にスクロールできます

SELECT 
    p1.product_name AS 安い商品,
    p1.price AS 安い価格,
    p2.product_name AS 高い商品,
    p2.price AS 高い価格,
    p2.price - p1.price AS 価格差
FROM products p1
JOIN products p2 
    ON p1.category = p2.category
    AND p1.price < p2.price
ORDER BY p1.category, 価格差 DESC
LIMIT 10;

解説:

p1.price < p2.price → 安い商品 → 高い商品の組み合わせ
p2.price - p1.price → 価格差を計算

問題 5応用

商品の組み合わせセット

異なる2つの商品の組み合わせと、そのセット価格（合計）を表示してください。セット価格の高い順に10件表示。

※横にスクロールできます

SELECT 
    p1.product_name AS 商品1,
    p1.price AS 価格1,
    p2.product_name AS 商品2,
    p2.price AS 価格2,
    p1.price + p2.price AS セット価格
FROM products p1
CROSS JOIN products p2
WHERE p1.product_id < p2.product_id
ORDER BY セット価格 DESC
LIMIT 10;

解説:

CROSS JOINで全組み合わせを作成
WHEREで重複を避ける
価格を合計してセット価格を計算

問題 6応用

顧客の都道府県別ペア

同じ都道府県に住む顧客のペアを表示してください。（重複なし）

※横にスクロールできます

SELECT 
    c1.customer_name AS 顧客1,
    c2.customer_name AS 顧客2,
    c1.prefecture AS 都道府県
FROM customers c1
JOIN customers c2 
    ON c1.prefecture = c2.prefecture
    AND c1.customer_id < c2.customer_id
ORDER BY c1.prefecture, c1.customer_name
LIMIT 20;

解説:

同じ都道府県で結合
customer_id < で重複を避ける

問題 7チャレンジ

数字の組み合わせ

1から5までの数字で、異なる2つの数字の組み合わせと、その合計を表示してください。

※横にスクロールできます

SELECT 
    n1.num AS 数字1,
    n2.num AS 数字2,
    n1.num + n2.num AS 合計
FROM (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 
    UNION ALL SELECT 4 UNION ALL SELECT 5
) n1
CROSS JOIN (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 
    UNION ALL SELECT 4 UNION ALL SELECT 5
) n2
WHERE n1.num < n2.num
ORDER BY n1.num, n2.num;

解説:

UNION ALLで数字のテーブルを作成
CROSS JOINで組み合わせ
n1.num < n2.num で重複を避ける

問題 8チャレンジ

7日間のカレンダー作成

2024年1月1日から7日間の日付リストを作成してください。

※横にスクロールできます

SELECT DATE('2024-01-01', '+' || (n - 1) || ' days') AS 日付
FROM (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
) numbers
ORDER BY 日付;

解説:

1〜7の数字テーブルを作成
DATE('2024-01-01', '+N days') でN日後の日付を計算
n-1 で 0日後（1/1）、1日後（1/2）...6日後（1/7）を生成

問題 9チャレンジ

共通購入商品の分析

同じ商品を購入したことがある顧客のペアと、共通して購入した商品数を表示してください。（共通商品2つ以上）

※横にスクロールできます

SELECT 
    c1.customer_name AS 顧客1,
    c2.customer_name AS 顧客2,
    COUNT(DISTINCT o1.product_id) AS 共通商品数
FROM orders o1
JOIN orders o2 
    ON o1.product_id = o2.product_id
    AND o1.customer_id < o2.customer_id
JOIN customers c1 ON o1.customer_id = c1.customer_id
JOIN customers c2 ON o2.customer_id = c2.customer_id
GROUP BY c1.customer_id, c2.customer_id, c1.customer_name, c2.customer_name
HAVING COUNT(DISTINCT o1.product_id) >= 2
ORDER BY 共通商品数 DESC
LIMIT 10;

解説:

自己結合で同じ商品を買った注文を結合
顧客テーブルをJOINして名前を取得
HAVINGで共通商品2つ以上に絞り込み

問題 10チャレンジ

全商品×全日付の売上マトリックス

過去7日間の各日付と全商品の組み合わせを作り、売上がある場合は金額、ない場合は0を表示してください。

※横にスクロールできます

WITH date_range AS (
    SELECT DATE('now', '-' || (n - 1) || ' days') AS date
    FROM (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    )
)
SELECT 
    d.date AS 日付,
    p.product_name AS 商品名,
    COALESCE(SUM(o.total_amount), 0) AS 売上
FROM date_range d
CROSS JOIN products p
LEFT JOIN orders o 
    ON DATE(o.order_date) = d.date 
    AND o.product_id = p.product_id
GROUP BY d.date, p.product_id, p.product_name
ORDER BY d.date DESC, p.product_name
LIMIT 30;

解説:

CTEで日付リストを作成
CROSS JOINで日付 × 商品の全組み合わせ
LEFT JOINで実際の売上を結合
COALESCEでNULLを0に変換

📝 Step 10 のまとめ

✅ 学んだこと

CROSS JOIN：2つのテーブルの全組み合わせを作る
自己結合：同じテーブルを自分自身と結合
別名（AS）：自己結合では必須（t1, t2 など）
重複を避ける：t1.id < t2.id で一方向のみ
階層構造：組織図、カテゴリツリーの表現
欠損データの補完：CROSS JOIN + LEFT JOIN

📌 重要な構文パターン

※横にスクロールできます

-- CROSS JOIN: 全組み合わせ
SELECT * FROM テーブル1 CROSS JOIN テーブル2;
-- 結果行数 = テーブル1の行数 × テーブル2の行数

-- 自己結合: 同じテーブルを2回使う
SELECT * 
FROM テーブル AS t1 
JOIN テーブル AS t2 ON t1.列 = t2.列;
-- 別名（AS）は必須!

-- 重複を避ける
WHERE t1.id < t2.id  -- 一方向のみに限定

-- 階層構造（親子関係）
SELECT 子.名前, 親.名前 AS 親の名前
FROM テーブル AS 子
LEFT JOIN テーブル AS 親 ON 子.親ID = 親.ID;

-- 欠損データの補完
SELECT * FROM マスター
CROSS JOIN 日付リスト
LEFT JOIN 実データ ON ...

⚠️ 注意点

CROSS JOINは結果が膨大になる可能性（行数 × 行数）
自己結合では必ず別名を使う
重複を避ける条件（< や >）を忘れずに
パフォーマンスに注意（インデックスを活用）

❓ よくある質問

Q1: CROSS JOINはいつ使いますか?

全ての組み合わせが必要な場合に使います。

商品 × サイズ × カラーの全バリエーション
日付 × 時間帯のカレンダー作成
欠損データの補完（全日付×全商品の売上表など）

Q2: 自己結合で別名は必須ですか?

はい、必須です。同じテーブルを2回使うので、t1、t2のように別名で区別しないとエラーになります。どちらのテーブルの列か分からなくなるためです。

Q3: t1.id < t2.id の意味は?

重複を避けるためです。

!=（等しくない）→ 両方向のペアが出る（A-B と B-A）
<（より小さい）→ 一方向のみ（A-B だけ）

ペアの重複を避けたい場合は<を使いましょう。

Q4: CROSS JOINは遅くないですか?

大きなテーブル同士だと非常に遅くなります。結果の行数は「行数 × 行数」なので、1万行 × 1万行 = 1億行になります。必ず小さなテーブル同士で使うか、WHERE句で絞り込みましょう。

Q5: 階層構造は何段階まで扱えますか?

SQLではJOINを繰り返すことで何段階でも扱えますが、深い階層（5階層以上など）は再帰クエリ（WITH RECURSIVE）を使う方が効率的です。

Q6: 実務でよく使うのは?

自己結合の方がよく使います。特に、階層構造（組織図、カテゴリ）や、同じテーブル内での比較（価格比較、期間比較）で活躍します。CROSS JOINは特殊な用途ですが、欠損データの補完には非常に便利です。

🎓 次のステップでは

Step 11: 複雑な結合パターンでは、より高度な結合テクニックを学びます。複数テーブルの結合、条件付き結合、アンチジョインなど、実務で使える応用パターンをマスターしましょう!

📝

学習メモ

SQL応用・パフォーマンス最適化 - Step 10

📋 過去のメモ一覧 ▼