話を始める前にまずは簡単によく使うSQLの結合公文を復習していきます。
- INNER JOIN (内部結合) - LEFT OUTER JOIN (左外部結合) - RIGHT OUTER JOIN (右外部結合) - FULL OUTER JOIN (完全外部結合)
このままの形式で書くことはあまりありません。基本的に
- INNER JOIN ⇒ JOIN - LEFT OUTER JOIN ⇒ LEFT JOIN - RIGHT OUTER JOIN ⇒ RIGHT JOIN
といった省略形で書くことが多いです。
LEFT JOINとRIGHT JOINがなんなのか最初に掴むまで苦労しましたが、いくつか手で書いてみるとわかります。
ここに雑な例を載せておきます。
mysql> SELECT * FROM photobook; +------+-----------------------+-----------------+------------+-------------+ | id | title | name | date | circulation | +------+-----------------------+-----------------+------------+-------------+ | 1 | パスポート | 白石麻衣 | 2017-02-07 | 273580 | | 2 | やさしい棘 | 橋本奈々未 | 2015-08-28 | 66125 | | 3 | 転調 | 生田絵梨花 | 2016-01-21 | 84598 | | 4 | 話を聞こうか。 | 衛藤美彩 | 2017-04-25 | 78821 | +------+-----------------------+-----------------+------------+-------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM member; +------+-----------------+------------+-------+ | id | name | age | class | +------+-----------------+------------+-------+ | 1 | 新内眞衣 | 1992-01-22 | 2 | | 2 | 白石麻衣 | 1992-08-20 | 1 | | 3 | 松村沙友理 | 1992-08-27 | 1 | | 4 | 衛藤美彩 | 1993-01-04 | 1 | | 5 | 伊藤かりん | 1993-05-16 | 2 | +------+-----------------+------------+-------+ 5 rows in set (0.00 sec)
たとえばでこんなテーブルを準備。
実際にやってみましょう。
内部結合 JOIN( = INNER JOIN)
mysql> SELECT * FROM member INNER JOIN photobook USING (name); +--------------+------+------------+-------+------+-----------------------+------------+-------------+ | name | id | age | class | id | title | date | circulation | +--------------+------+------------+-------+------+-----------------------+------------+-------------+ | 白石麻衣 | 2 | 1992-08-20 | 1 | 1 | パスポート | 2017-02-07 | 273580 | | 衛藤美彩 | 4 | 1993-01-04 | 1 | 4 | 話を聞こうか。 | 2017-04-25 | 78821 | +--------------+------+------------+-------+------+-----------------------+------------+-------------+ 2 rows in set (0.00 sec)
name というカラムで両者をつないでどっちにもあるものを引っ張ってきてます。
（余談だけどこの辺を解説しているサイトとか記事は結合因子を数値のカラムを使って見せるのでややこしく見えてしまう。上みたいに人名だったらどう結合されてるのかわかりやすい！...はず。）
左外部結合 LEFT JOIN
mysql> SELECT * FROM member LEFT JOIN photobook USING (name); +-----------------+------+------------+-------+------+-----------------------+------------+-------------+ | name | id | age | class | id | title | date | circulation | +-----------------+------+------------+-------+------+-----------------------+------------+-------------+ | 白石麻衣 | 2 | 1992-08-20 | 1 | 1 | パスポート | 2017-02-07 | 273580 | | 衛藤美彩 | 4 | 1993-01-04 | 1 | 4 | 話を聞こうか。 | 2017-04-25 | 78821 | | 新内眞衣 | 1 | 1992-01-22 | 2 | NULL | NULL | NULL | NULL | | 松村沙友理 | 3 | 1992-08-27 | 1 | NULL | NULL | NULL | NULL | | 伊藤かりん | 5 | 1993-05-16 | 2 | NULL | NULL | NULL | NULL | +-----------------+------+------------+-------+------+-----------------------+------------+-------------+ 5 rows in set (0.01 sec)
ここでは向かって左側のテーブル、操作する主体のテーブルをmemberとしています。
そのため乃木坂年上5名の名前がちゃんと出てきます。
当然、新内眞衣、松村沙友理、伊藤かりんの写真集データは（ここでは）存在しないため、写真集部分のデータはNULLが入っています。
また逆に橋本奈々未、生田絵梨花の写真集情報は参照されていないことがわかります。
右外部結合 RIGHT JOIN
mysql> SELECT * FROM member RIGHT JOIN photobook USING (name); +-----------------+------+-----------------------+------------+-------------+------+------------+-------+ | name | id | title | date | circulation | id | age | class | +-----------------+------+-----------------------+------------+-------------+------+------------+-------+ | 白石麻衣 | 1 | パスポート | 2017-02-07 | 273580 | 2 | 1992-08-20 | 1 | | 衛藤美彩 | 4 | 話を聞こうか。 | 2017-04-25 | 78821 | 4 | 1993-01-04 | 1 | | 橋本奈々未 | 2 | やさしい棘 | 2015-08-28 | 66125 | NULL | NULL | NULL | | 生田絵梨花 | 3 | 転調 | 2016-01-21 | 84598 | NULL | NULL | NULL | +-----------------+------+-----------------------+------------+-------------+------+------------+-------+ 4 rows in set (0.00 sec)
こちらは打って変わって操作の主体をphotobook側のテーブルとしています。
生田絵梨花と橋本奈々未のrowがある一方で、彼女たちの年齢のデータはありません。なぜなら二人はmember テーブルに存在していないからです。
FULL OUTER JOINは説明していないけれど、実はMySQLでは実装されておらず、簡単に例示を示すことができません。
とはいえ、名前からだいたい察しが付くと思います。
左のテーブル（メインで操作するテーブル）にしかないものも表示するLEFT JOINと
右のテーブル（結合対象のテーブル）にしかないものも表示するRIGHT JOIN、
どちらも表示するのがFULL OUTER JOINだ、たぶん。（使ったことない。）
とまあ、雑ではあるが基本的なJOINの使い方は紹介できた気がします。
そして、ここからが今回の本題です。
クロス結合 (CROSS JOIN)とは何者だ？
ざっくり説明すると2つのテーブルを各データごとに結合してすべての組み合わせデータを取得する結合方法がクロス結合です。
何を言っているのかさっぱりだと思いますので、まとめていきます。
まずは次のようなテーブルを準備。
mysql> SELECT * FROM last_name; +-------+-----------+ | group | last_name | +-------+-----------+ | 1 | ITO | | 2 | SAITO | +-------+-----------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM first_name; +------+------------+-------+ | id | first_name | group | +------+------------+-------+ | 1 | かりん | 1 | | 2 | 理々杏 | 1 | | 3 | 純奈 | 1 | | 4 | 万理華 | 1 | | 5 | 寧々 | 1 | | 6 | 優里 | 2 | | 7 | 飛鳥 | 2 | | 8 | ちはる | 2 | +------+------------+-------+ 8 rows in set (0.00 sec)
まずは先程も説明したINNER JOINを使うとどうなるか。
mysql> SELECT `group`, `last_name`, `first_name` FROM last_name JOIN first_name USING (`group`); +-------+-----------+------------+ | group | last_name | first_name | +-------+-----------+------------+ | 1 | ITO | かりん | | 1 | ITO | 理々杏 | | 1 | ITO | 純奈 | | 1 | ITO | 万理華 | | 1 | ITO | 寧々 | | 2 | SAITO | 優里 | | 2 | SAITO | 飛鳥 | | 2 | SAITO | ちはる | +-------+-----------+------------+ 8 rows in set (0.00 sec)
特に引っかかることもないでしょう。
2つのテーブルのgroupカラムを使って等しいロウ同士を結合しています。
では、たとえば、（あくまでたとえば）ですが「この人はITO？それともSAITO？どっち？」みたいなクイズをやりたいとします。
（まあ、率直に言って「オタクを舐めるな」、って問題ですが…）
そんなときにCROSS JOINを使うとすべての組み合わせを見ることができます。
mysql> SELECT last_name.`group`, `last_name`, `first_name` FROM last_name CROSS JOIN first_name; +-------+-----------+------------+ | group | last_name | first_name | +-------+-----------+------------+ | 1 | ITO | かりん | | 2 | SAITO | かりん | | 1 | ITO | 理々杏 | | 2 | SAITO | 理々杏 | | 1 | ITO | 純奈 | | 2 | SAITO | 純奈 | | 1 | ITO | 万理華 | | 2 | SAITO | 万理華 | | 1 | ITO | 寧々 | | 2 | SAITO | 寧々 | | 1 | ITO | 優里 | | 2 | SAITO | 優里 | | 1 | ITO | 飛鳥 | | 2 | SAITO | 飛鳥 | | 1 | ITO | ちはる | | 2 | SAITO | ちはる | +-------+-----------+------------+ 16 rows in set (0.00 sec)
こんな感じで先程まで説明してきた結合とは違い、どこかのカラムを参照して等しいとかではなく単純な組み合わせを計算（直積）しているわけです。そのため結合条件のUSINGやONは必要がありません。
結論から言うとクロス結合を実務で使う場面はそう多くないです。
現にここ半年ほど様々なクエリを書いてきましたが「CROSS JOINが必要だ！」と感じたことはありませんでした。
つい先日まで…。
今回どういうケースでクロス結合が必要となったのか。
それはズバリ言ってしまうと横持ちのデータを縦持ちに変換するケースです！！
（タイトルに書いてあるね＼(^o^)／）
横持ちテーブルと縦持ちテーブル
もはやダミーデータを準備するのも面倒になってしまったのでスクショで失礼します。
横持ちデータとは上のようないわゆるよく見るタイプのテーブル構造のデータの持ち方のこと。
一方で縦持ちテーブルは次のようなものです。
おわかりいただけるでしょうか？
1行に対して1つの情報のみを格納しているのが縦持ちのデータ構造です。
後者は普段あまりみない上に一見メリットが無いように見えます。
しかし、たとえば横持ちのデータ構造で異様にカラム数が膨れ上がっているケースとか、あるいはデータがNULLばかりとか（スパースとかっていうらしい）のケースでは縦持ちにして整理したほうが格段に管理が楽になります。
それこそ今、上にスクショを張ったメンバー情報を管理するケースなどでは縦持ちの方が自然に感じます。（もちろん、あえてそういう例を選んでいます。）
とはいえ、常に「自然に感じる（＝操作がしやすい）」状態でデータは保存されているとは限りません。
場合によっては臨機応変に縦持ち構造のデータを横持ち構造に、横持ち構造のデータを縦持ち構造に変換する必要があります。
では実際にまず、縦持ち⇒横持ちの変換を見てみましょう。
（こちらの変換ではCROSS JOINを使う必要はありません。）
mysql> select * from single; +-----------------------------------------+-----------------+ | title | center | +-----------------------------------------+-----------------+ | ぐるぐるカーテン | 生駒里奈 | | おいでシャンプー | 生駒里奈 | | 走れ！Bicycle | 生駒里奈 | | 制服のマネキン | 生駒里奈 | | 君の名は希望 | 生駒里奈 | | ガールズルール | 白石麻衣 | | バレッタ | 堀未央奈 | | 気づいたら片想い | 西野七瀬 | | 夏のFree&Easy | 西野七瀬 | | 何度目の青空か？ | 生田絵梨花 | | 命は美しい | 西野七瀬 | | 太陽ノック | 生駒里奈 | | 今、話したい誰かがいる | 西野七瀬 | | 今、話したい誰かがいる | 白石麻衣 | | ハルジオンが咲く頃 | 深川麻衣 | | 裸足でSummer | 齋藤飛鳥 | | サヨナラの意味 | 橋本奈々未 | | インフルエンサー | 白石麻衣 | | インフルエンサー | 西野七瀬 | | 逃げ水 | 大園桃子 | | 逃げ水 | 与田祐希 | | いつかできるから今日できる | 西野七瀬 | | いつかできるから今日できる | 齋藤飛鳥 | | シンクロニシティ | 白石麻衣 | +-----------------------------------------+-----------------+ 24 rows in set (0.00 sec)
こんなデータを準備してみました。
さて早速実践してみたいと思うんですが、そのまえに今回はMySQLでの話なのでウィンドウ関数が使えません。（まあ最近のやつは使えるんだけど…）
ROW_NUMBERが使えないため、今回必要となる各メンバーが何枚センターを務めているのかを示す連番は自分でうまく計算してやらならければなりません。
先にその方法だけ示しておきます。
mysql> SET @num = 0; Query OK, 0 rows affected (0.00 sec) mysql> SET @center = null; Query OK, 0 rows affected (0.00 sec) mysql> SELECT IF(@center <> center, @num:=1, @num:=@num+1) AS num, -> @center:=center AS center, -> title -> FROM single ORDER BY center; +------+-----------------+-----------------------------------------+ | num | center | title | +------+-----------------+-----------------------------------------+ | 1 | 与田祐希 | 逃げ水 | | 1 | 堀未央奈 | バレッタ | | 1 | 大園桃子 | 逃げ水 | | 1 | 橋本奈々未 | サヨナラの意味 | | 1 | 深川麻衣 | ハルジオンが咲く頃 | | 1 | 生田絵梨花 | 何度目の青空か？ | | 1 | 生駒里奈 | ぐるぐるカーテン | | 2 | 生駒里奈 | おいでシャンプー | | 3 | 生駒里奈 | 走れ！Bicycle | | 4 | 生駒里奈 | 制服のマネキン | | 5 | 生駒里奈 | 君の名は希望 | | 6 | 生駒里奈 | 太陽ノック | | 1 | 白石麻衣 | ガールズルール | | 2 | 白石麻衣 | 今、話したい誰かがいる | | 3 | 白石麻衣 | インフルエンサー | | 4 | 白石麻衣 | シンクロニシティ | | 1 | 西野七瀬 | 気づいたら片想い | | 2 | 西野七瀬 | 夏のFree&Easy | | 3 | 西野七瀬 | 命は美しい | | 4 | 西野七瀬 | 今、話したい誰かがいる | | 5 | 西野七瀬 | インフルエンサー | | 6 | 西野七瀬 | いつかできるから今日できる | | 1 | 齋藤飛鳥 | 裸足でSummer | | 2 | 齋藤飛鳥 | いつかできるから今日できる | +------+-----------------+-----------------------------------------+ 24 rows in set (0.00 sec)
こちらのStackoverflowを参考にしました。
これをサブクエリとして応用して次のように書くと縦持ち⇒横持ちへと変換できます。
SELECT single_2.center, MAX(CASE single_2.num WHEN 1 THEN single_2.title END) AS cd_1, MAX(CASE single_2.num WHEN 2 THEN single_2.title END) AS cd_2, MAX(CASE single_2.num WHEN 3 THEN single_2.title END) AS cd_3, MAX(CASE single_2.num WHEN 4 THEN single_2.title END) AS cd_4, MAX(CASE single_2.num WHEN 5 THEN single_2.title END) AS cd_5, MAX(CASE single_2.num WHEN 6 THEN single_2.title END) AS cd_6 FROM (SELECT IF(@center <> center, @num:=1, @num:=@num+1) AS num, @center:=center AS center, title FROM single ORDER BY center) AS single_2 GROUP BY single_2.center; +-----------------+-----------------------------+-----------------------------------------+--------------------------+-----------------------------------+--------------------------+-----------------------------------------+ | center | cd_1 | cd_2 | cd_3 | cd_4 | cd_5 | cd_6 | +-----------------+-----------------------------+-----------------------------------------+--------------------------+-----------------------------------+--------------------------+-----------------------------------------+ | 与田祐希 | 逃げ水 | NULL | NULL | NULL | NULL | NULL | | 堀未央奈 | バレッタ | NULL | NULL | NULL | NULL | NULL | | 大園桃子 | 逃げ水 | NULL | NULL | NULL | NULL | NULL | | 橋本奈々未 | サヨナラの意味 | NULL | NULL | NULL | NULL | NULL | | 深川麻衣 | ハルジオンが咲く頃 | NULL | NULL | NULL | NULL | NULL | | 生田絵梨花 | 何度目の青空か？ | NULL | NULL | NULL | NULL | NULL | | 生駒里奈 | ぐるぐるカーテン | おいでシャンプー | 走れ！Bicycle | 制服のマネキン | 君の名は希望 | 太陽ノック | | 白石麻衣 | ガールズルール | 今、話したい誰かがいる | インフルエンサー | シンクロニシティ | NULL | NULL | | 西野七瀬 | 気づいたら片想い | 夏のFree&Easy | 命は美しい | 今、話したい誰かがいる | インフルエンサー | いつかできるから今日できる | | 齋藤飛鳥 | 裸足でSummer | いつかできるから今日できる | NULL | NULL | NULL | NULL | +-----------------+-----------------------------+-----------------------------------------+--------------------------+-----------------------------------+--------------------------+-----------------------------------------+ 10 rows in set (0.00 sec)
ウィンドウ関数を使ってないことで本題を見失いそうですが、縦持ちを横持ちに変更するコツはサブクエリの値をCASE句を使って横持ちに変換している点です。
つまり、集計関数はMAXである必要はありません。AVGでもSUMでも良いわけです。
はい、では次は横持ちから縦持ちに変換するパターンを見ていきます。
mysql> SELECT * FROM single_3; +-----------------+-----------------------------+-----------------------------------------+--------------------------+-----------------------------------+--------------------------+--------------------------------------+ | center | cd_1 | cd_2 | cd_3 | cd_4 | cd_5 | cd_6 | +-----------------+-----------------------------+-----------------------------------------+--------------------------+-----------------------------------+--------------------------+--------------------------------------+ | 与田祐希 | 逃げ水 | NULL | NULL | NULL | NULL | NULL | | 大園桃子 | 逃げ水 | NULL | NULL | NULL | NULL | NULL | | 堀未央奈 | バレッタ | NULL | NULL | NULL | NULL | NULL | | 橋本奈々未 | サヨナラの意味 | NULL | NULL | NULL | NULL | NULL | | 深川麻衣 | ハルジオンが咲く頃 | NULL | NULL | NULL | NULL | NULL | | 生田絵梨花 | 何度目の青空か？ | NULL | NULL | NULL | NULL | NULL | | 齋藤飛鳥 | 裸足でSummer | いつかできるから今日できる | NULL | NULL | NULL | NULL | | 白石麻衣 | ガールズルール | 今、話したい誰かがいる | インフルエンサー | シンクロニシティ | NULL | NULL | | 生駒里奈 | ぐるぐるカーテン | おいでシャンプー | 走れ！Bicycle | 制服のマネキン | 君の名は希望 | 太陽ノック | | 西野七瀬 | 気づいたら片想い | 夏のFree&Easy | 命は美しい | 今、話したい誰かがいる | インフルエンサー | つかできるから今日できる | +-----------------+-----------------------------+-----------------------------------------+--------------------------+-----------------------------------+--------------------------+--------------------------------------+ 10 rows in set (0.00 sec)
まず先に、先程の最終結果と同じ構造の横持ちのデータを準備します。
そして、ここがキモなんですが連番を管理するために一旦pivotテーブルを作ります。
mysql> SELECT * FROM cd_num; +------+ | num | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +------+ 6 rows in set (0.00 sec)
こういうテーブルです。CROSS JOINつまり直積計算をするためだけにテーブルを作ります。
さてこの発想さえできてしまえばあとは簡単です。
mysql> SELECT center, -> CASE num -> WHEN 1 THEN single_3.cd_1 -> WHEN 2 THEN single_3.cd_2 -> WHEN 3 THEN single_3.cd_3 -> WHEN 4 THEN single_3.cd_4 -> WHEN 5 THEN single_3.cd_5 -> WHEN 6 THEN single_3.cd_6 -> END AS title -> FROM single_3 CROSS JOIN cd_num;
これを実行すると…
+-----------------+-----------------------------------------+ | center | title | +-----------------+-----------------------------------------+ | 与田祐希 | 逃げ水 | | 与田祐希 | NULL | | 与田祐希 | NULL | | 与田祐希 | NULL | | 与田祐希 | NULL | | 与田祐希 | NULL | | 大園桃子 | 逃げ水 | | 大園桃子 | NULL | | 大園桃子 | NULL | | 大園桃子 | NULL | | 大園桃子 | NULL | | 大園桃子 | NULL | | 堀未央奈 | バレッタ | | 堀未央奈 | NULL | | 堀未央奈 | NULL | | 堀未央奈 | NULL | | 堀未央奈 | NULL | | 堀未央奈 | NULL | | 橋本奈々未 | サヨナラの意味 | | 橋本奈々未 | NULL | | 橋本奈々未 | NULL | | 橋本奈々未 | NULL | | 橋本奈々未 | NULL | | 橋本奈々未 | NULL | | 深川麻衣 | ハルジオンが咲く頃 | | 深川麻衣 | NULL | | 深川麻衣 | NULL | | 深川麻衣 | NULL | | 深川麻衣 | NULL | | 深川麻衣 | NULL | | 生田絵梨花 | 何度目の青空か？ | | 生田絵梨花 | NULL | | 生田絵梨花 | NULL | | 生田絵梨花 | NULL | | 生田絵梨花 | NULL | | 生田絵梨花 | NULL | | 齋藤飛鳥 | 裸足でSummer | | 齋藤飛鳥 | いつかできるから今日できる | | 齋藤飛鳥 | NULL | | 齋藤飛鳥 | NULL | | 齋藤飛鳥 | NULL | | 齋藤飛鳥 | NULL | | 白石麻衣 | ガールズルール | | 白石麻衣 | 今、話したい誰かがいる | | 白石麻衣 | インフルエンサー | | 白石麻衣 | シンクロニシティ | | 白石麻衣 | NULL | | 白石麻衣 | NULL | | 生駒里奈 | ぐるぐるカーテン | | 生駒里奈 | おいでシャンプー | | 生駒里奈 | 走れ！Bicycle | | 生駒里奈 | 制服のマネキン | | 生駒里奈 | 君の名は希望 | | 生駒里奈 | 太陽ノック | | 西野七瀬 | 気づいたら片想い | | 西野七瀬 | 夏のFree&Easy | | 西野七瀬 | 命は美しい | | 西野七瀬 | 今、話したい誰かがいる | | 西野七瀬 | インフルエンサー | | 西野七瀬 | つかできるから今日できる | +-----------------+-----------------------------------------+ 60 rows in set (0.00 sec)
こうなります。
全部を一旦縦持ちにしたはいいけど大抵のケースに置いてNULLは必要ない。
よって、今度はコレ自体をサブクエリにしてNULLのものを消しちゃいます。
mysql> SELECT sub.* FROM (SELECT center, -> CASE num -> WHEN 1 THEN single_3.cd_1 -> WHEN 2 THEN single_3.cd_2 -> WHEN 3 THEN single_3.cd_3 -> WHEN 4 THEN single_3.cd_4 -> WHEN 5 THEN single_3.cd_5 -> WHEN 6 THEN single_3.cd_6 -> END AS title -> FROM single_3 CROSS JOIN cd_num) sub -> WHERE sub.title IS NOT NULL; +-----------------+-----------------------------------------+ | center | title | +-----------------+-----------------------------------------+ | 与田祐希 | 逃げ水 | | 大園桃子 | 逃げ水 | | 堀未央奈 | バレッタ | | 橋本奈々未 | サヨナラの意味 | | 深川麻衣 | ハルジオンが咲く頃 | | 生田絵梨花 | 何度目の青空か？ | | 齋藤飛鳥 | 裸足でSummer | | 齋藤飛鳥 | いつかできるから今日できる | | 白石麻衣 | ガールズルール | | 白石麻衣 | 今、話したい誰かがいる | | 白石麻衣 | インフルエンサー | | 白石麻衣 | シンクロニシティ | | 生駒里奈 | ぐるぐるカーテン | | 生駒里奈 | おいでシャンプー | | 生駒里奈 | 走れ！Bicycle | | 生駒里奈 | 制服のマネキン | | 生駒里奈 | 君の名は希望 | | 生駒里奈 | 太陽ノック | | 西野七瀬 | 気づいたら片想い | | 西野七瀬 | 夏のFree&Easy | | 西野七瀬 | 命は美しい | | 西野七瀬 | 今、話したい誰かがいる | | 西野七瀬 | インフルエンサー | | 西野七瀬 | つかできるから今日できる | +-----------------+-----------------------------------------+ 24 rows in set, 24 warnings (0.00 sec)
完成です。
おわり
今回は「クロス結合とはそもそも何なのか」「具体的にはこんなケースで活用できるよ！」というお話でした。
