MySQL の CROSS JOIN

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

CROSS JOIN は、一方のテーブルのすべての行をもう一方のテーブルのすべての行と組み合わせます。何も一致させないため、ON 条件は書きません。すべての組み合わせが欲しいときに求める結合であり、ON 句を書き忘れたときに事故として得る結合でもあります。始める前に mysql -u root -p sakila でクライアントを開いてください。

CROSS JOIN の仕組み

SELECT COLUMN_LIST
FROM FIRST_TABLE
CROSS JOIN SECOND_TABLE;

COLUMN_LIST を必要な列に、FIRST_TABLE と SECOND_TABLE を組み合わせるテーブルに置き換えます。結果は可能なすべての組み合わせについて 1 行を持つため、そのサイズは 1 つ目のテーブルの行数に 2 つ目の行数を掛けたものです。6 行と 2 行をクロスすると 12 行になります。

例

すべての組み合わせ

Sakila には 6 つの言語と 2 つの店舗があります。

SELECT l.name, s.store_id
FROM language AS l
CROSS JOIN store AS s
ORDER BY l.name, s.store_id;

+----------+----------+
| name     | store_id |
+----------+----------+
| English  |        1 |
| English  |        2 |
| French   |        1 |
| French   |        2 |
| German   |        1 |
| German   |        2 |
| Italian  |        1 |
| Italian  |        2 |
| Japanese |        1 |
| Japanese |        2 |
| Mandarin |        1 |
| Mandarin |        2 |
+----------+----------+
12 rows in set (0.00 sec)

すべての言語が店舗ごとに 1 回現れます。ここには、店舗がその言語の映画を在庫しているという意味は何もありません。行は組み合わせであって、データについての事実ではないのです。それがこのクエリの要点です。何かが起きたかどうかにかかわらずすべての組み合わせについて 1 行が欲しいとき、このような格子が出発点の形になります。

カンマは古い書き方

SELECT l.name, s.store_id
FROM language AS l, store AS s
ORDER BY l.name, s.store_id;

+----------+----------+
| name     | store_id |
+----------+----------+
| English  |        1 |
| English  |        2 |
| French   |        1 |
| French   |        2 |
| German   |        1 |
| German   |        2 |
| Italian  |        1 |
| Italian  |        2 |
| Japanese |        1 |
| Japanese |        2 |
| Mandarin |        1 |
| Mandarin |        2 |
+----------+----------+
12 rows in set (0.00 sec)

同じ 12 行です。FROM 内の 2 つのテーブルの間のカンマはクロス結合です。古い SQL で出会うことになり、多くの場合、一致条件は下の WHERE にあります。これは動作しますが、どの種類の結合を読んでいるのかを隠してしまいます。カンマは JOIN より優先順位が低いため、ON 句はカンマの反対側にあるテーブルに届きません。この 2 つの書き方は実際のクエリで一緒に現れることがあり、そのときうまくいかないのがこれです。

SELECT COUNT(*) FROM film f, language l JOIN store s ON s.store_id = f.language_id;

ERROR 1054 (42S22): Unknown column 'f.language_id' in 'on clause'

クロス結合を意図するときは CROSS JOIN と書き、1 つのクエリでは 1 つの書き方に統一してください。キーワードもこのルールを強制しません。MySQL は CROSS JOIN に付いた ON 句を受け入れ、INNER JOIN として振る舞うため、クエリ内の単語は、得られた結合の証拠にはなりません。

SELECT COUNT(*) AS rows_returned
FROM film AS f CROSS JOIN language AS l ON f.language_id = l.language_id;

+---------------+
| rows_returned |
+---------------+
|          1000 |
+---------------+
1 row in set (0.00 sec)

6,000 ではなく 1,000 です。すべての映画が自分の言語と一致しました。結合が何をするのかを知るには、キーワードではなく ON 句を読んでください。

数はすぐに大きくなる

12 行は無害です。普段クエリするテーブルに対する同じ操作はそうではありません。

SELECT f.title, l.name
FROM film AS f
CROSS JOIN language AS l
ORDER BY f.title, l.name;

+-----------------------------+----------+
| title                       | name     |
+-----------------------------+----------+
| ACADEMY DINOSAUR            | English  |
| ACADEMY DINOSAUR            | French   |
| ACADEMY DINOSAUR            | German   |
...
+-----------------------------+----------+
6000 rows in set (0.01 sec)

1,000 本の映画かける 6 つの言語は 6,000 行で、各映画が 6 つの言語すべてを持つことになります。10,000 行のテーブル 2 つをクロスすると答えは 1 億行になり、一瞬で終わるはずだったクエリが、誰かが止めるまで走り続けるのはこうしてです。 MySQL は ON 句のない INNER JOIN を受け入れ、クロス結合として扱います。そのため、結合が不自然に切りの良い、不自然に大きな数を返したとき、最初に確認すべきは ON の欠落です。INNER JOIN のレッスンでその事故を示します。

まとめ

一方のテーブルのすべての行をもう一方のすべての行と組み合わせるには CROSS JOIN を使います。
何も一致させないため、ON 句は書きません。
MySQL は CROSS JOIN ... ON をそれでも受け入れ、内部結合として扱うと考えてください。
実行する前に、2 つの行数を掛けてサイズを知っておきます。
FROM 内のテーブル間のカンマはクロス結合と読み、1 つのクエリでは 1 つの書き方に統一します。
結合が切りの良い大きな行数を返したら、ON の欠落を疑います。

MySQL Tutorial

ガイド

データの保存

クエリの作成

データの保護

パフォーマンスの最適化

AI で構築する

MySQL の運用

アプリケーションの接続

拡張機能の作成

VillageSQL is a drop-in replacement for MySQL with extensions.

CROSS JOIN の仕組み

例

すべての組み合わせ

カンマは古い書き方

数はすぐに大きくなる

まとめ

関連項目