LoginSignup
1

More than 1 year has passed since last update.

CROSS JOIN(交差結合)って何?

Last updated at Posted at 2021-04-21

はじめに

SQLにはCROSS JOINという結合構文がありますが、「これ使ったことないな。なんだろう?」と思ったので調べたことをメモしておきます。

CROSS JOINとは?

CROSS JOINは、各テーブルのレコードの全ての組み合わせを返します。別の言い方をすると、各レコードを要素とするテーブルという集合同士の直積です。

具体的には、以下のような結果を返します。

mysql> SELECT * FROM books;
+----+--------------------+----------+
| id | name               | owner_id |
+----+--------------------+----------+
|  1 | Clean Code         |        1 |
|  2 | Clean Architecture |        1 |
|  3 | SQL Antipatterns   |        2 |
+----+--------------------+----------+

mysql> SELECT * FROM users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
|  3 | Mike  |
+----+-------+


mysql> SELECT 
             u.id AS user_id,
             u.name AS user_name,
             b.name AS book_name
       FROM users AS u CROSS JOIN books AS b;
+---------+-----------+--------------------+
| user_id | user_name | book_name          |
+---------+-----------+--------------------+
|       3 | Mike      | Clean Code         |
|       2 | Bob       | Clean Code         |
|       1 | Alice     | Clean Code         |
|       3 | Mike      | Clean Architecture |
|       2 | Bob       | Clean Architecture |
|       1 | Alice     | Clean Architecture |
|       3 | Mike      | SQL Antipatterns   |
|       2 | Bob       | SQL Antipatterns   |
|       1 | Alice     | SQL Antipatterns   |
+---------+-----------+--------------------+

3 × 3 = 9個の結果が帰ってきますね。

ユーザーと所有している本の一覧は以下のように取得できます。

mysql> SELECT 
             u.id AS user_id,
             u.name AS user_name,
             b.name AS book_name
       FROM users AS u CROSS JOIN books AS b 
       WHERE u.id = b.owner_id;
+---------+-----------+--------------------+
| user_id | user_name | book_name          |
+---------+-----------+--------------------+
|       1 | Alice     | Clean Code         |
|       1 | Alice     | Clean Architecture |
|       2 | Bob       | SQL Antipatterns   |
+---------+-----------+--------------------+

これはINNER JOINを使う一般的な方法と結果は同じです。

mysql> SELECT 
             u.id AS user_id,
             u.name AS user_name,
             b.name AS book_name
       FROM users AS u JOIN books AS b 
       ON u.id = b.owner_id;
+---------+-----------+--------------------+
| user_id | user_name | book_name          |
+---------+-----------+--------------------+
|       1 | Alice     | Clean Code         |
|       1 | Alice     | Clean Architecture |
|       2 | Bob       | SQL Antipatterns   |
+---------+-----------+--------------------+

最後に

今のところCROSS JOINが必要になるケースは思いつきませんが、とりあえずどういう結果が帰ってくる構文なのかは理解できました。
使う機会あるのかな〜笑

参考

「Feedforce Developer Blog」cross join を知ると join が書きやすくなるよ、という話

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1