カテゴリー
アーカイブ
アーカイブ
社員のSです！
以前、初めてSQLのCROSS JOINが使用されているところを見たので、 どのような時に使われるのか調べてみました。
CROSS JOINとは？
CROSS JOINでは、2つのテーブルの全てのデータの組み合わせを取得することができます。
SELECT * FROM [テーブル1] CROSS JOIN [テーブル2];
上記のように結合すると、得られる結果のレコード数は [ テーブル1のレコード数 ] x [ テーブル2のレコード数 ] になります。
例えは、次のような2つのテーブルを用意します。
names テーブル
+------+
| name |
+------+
| A |
| B |
| C |
+------+
nums テーブル
+-----+
| num |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
この2つのテーブルを CROSS JOINするSQLはこうなって、
SELECT
names.name, nums.num
FROM
names
CROSS JOIN nums;
結果はこうなります。
+------+-----+
| name | num |
+------+-----+
| C | 1 |
| B | 1 |
| A | 1 |
| C | 2 |
| B | 2 |
| A | 2 |
| C | 3 |
| B | 3 |
| A | 3 |
+------+-----+
2つのテーブルの全てのレコードの組み合わせが取得できます。
今回やりたいこと
mysql> SELECT * FROM test;
+----+------------+------------+------------+---------+---------+---------+---------+---------+---------+
| id | name_1 | name_2 | name_3 | level_1 | level_2 | level_3 | score_1 | score_2 | score_3 |
+----+------------+------------+------------+---------+---------+---------+---------+---------+---------+
| 1 | テストA | テストB | テストC | 高 | 中 | 低 | 80 | 90 | 100 |
| 2 | テストD | テストE | テストF | 中 | 中 | 低 | 85 | 90 | 90 |
+----+------------+------------+------------+---------+---------+---------+---------+---------+---------+
2 rows in set (0.00 sec)
このようなテーブルがあって、カラム「name_[番号]」「level_[番号]」「score_[番号]」の _[番号] が共通するカラムを1レコードとして取得したい。
つまり、
こうしたい（横 -> 縦変換したい）。
この欲しい結果を取得するためのSQLは以下のようになって、
SELECT
CASE t2.num
WHEN 1 THEN name_1
WHEN 2 THEN name_2
WHEN 3 THEN name_3
END AS name,
CASE t2.num
WHEN 1 THEN level_1
WHEN 2 THEN level_2
WHEN 3 THEN level_3
END AS level,
CASE t2.num
WHEN 1 THEN score_1
WHEN 2 THEN score_2
WHEN 3 THEN score_3
END AS score
FROM
test t1
CROSS JOIN
(
SELECT 1 AS num UNION
SELECT 2 UNION
SELECT 3
) t2
ORDER BY name;
結果はこうなります。
+------------+-------+-------+
| name | level | score |
+------------+-------+-------+
| テストA | 高 | 80 |
| テストB | 中 | 90 |
| テストC | 低 | 100 |
| テストD | 中 | 85 |
| テストE | 中 | 90 |
| テストF | 低 | 90 |
+------------+-------+-------+
6 rows in set (0.00 sec)
欲しかった結果になりました！！
このSQLでやっていること
まず、この部分でt2テーブルを用意しています。
(
SELECT 1 AS num UNION
SELECT 2 UNION
SELECT 3
) t2
次に、CROSS JOINでt1（test）テーブルと、t2テーブルを結合して、
最後にCASE文でとる列を変えています。
まとめ
CROSS JOIN は滅多に使うことがないようですが、今回のように横持ちのデータを、縦持のデータに変換する際に使用されることがあるそうです。
勉強になりました！
