今度JOINしてくれるSQLくんです

この記事の続きです。

誰にでも伝わるようにSQLの色々を書き綴りたい。（願望）
そして前の記事を書いたときに、周りがたくさん褒めてくれて嬉しかった。
そんなわけで続きを書きます。

なぜJOINをするのか

物事には理由があります。
JOINをするのにも理由があります。

まず、前提として、多くの場合、データは複数のテーブルに分割されて存在しています。
そして、その複数に分割されたデータを統合して扱いたいのでJOINをするのです。

なぜ、統合して扱いたいのにテーブルが分かれているのでしょう？

そのメリットは前編で少し触れました。
しかし、折角なので前編とは違うメリットを紹介したいと思います。

それは、データを複数のテーブルに分割して保存すると「良いデータベース」になると言うことです。

良いデータベースとは

僕も必死こいて勉強してるところですが、良いデータベース（安心安全！使いやすい！etc...）であるためには、テーブルが適切に分割されている必要があります。

例として、SQL学園の履修管理テーブルを見てみましょう。

番号	名前	コース	履修講座	担当教師
1	田中	文系	数学I	高橋
2	田中	文系	英語	伊藤
3	山田	理系	数学I	高橋
4	山田	理系	数学III	渡辺
5	佐藤	理系	数学III	渡辺
6	鈴木	文系	数学I	高橋
7	鈴木	文系	英語	伊藤

あ、これは良くないですね。
どこが良くないか、わかりますか？

問題になるのは、数学Iを担当している高橋先生が異動になって山本先生に変わった時です。
1番と3番と6番、複数行の担当教師列を書き換えないといけませんね。

また、田中くんが一念発起して理転した時を想像してください。
1番と2番、これも2行のコース列を書き換えないといけません。

こういう、1つの変更で複数の操作が必要になる設計は良くありません。

なぜ良くないのかというと、そもそも何回も同じ処理で書き換えるのは面倒ですね。
データベースの能力を無駄に浪費してしまいます。

そして、もう一つ、安定性に問題があります。
高橋先生を山本先生に書き換えている途中に、停電が起きて書き換えが中断したらどうなるでしょう。
田中くんと山田くんの担当は山本先生、鈴木くんの担当は高橋先生、と変なことになってしまいます。
こう言うのを不整合といい、こうなってしまったデータベースを元に戻すのは大変です。

どうすれば良いのか

ここでテーブルの分割が活躍します。
次のように3つに分割してみましょう。

学生情報テーブル

学籍番号	名前	コース
1	田中	文系
2	山田	理系
3	佐藤	理系
4	鈴木	文系

講座情報テーブル

講座番号	名前	担当教師
1	数学I	高橋
2	数学III	渡辺
3	英語	伊藤

履修情報テーブル

学籍番号	講座番号
1	1
1	3
2	1
2	2
3	2
4	1
4	3

この様になっていると、異動や文転理転、あるいは苗字の変更などがとても簡単に行えます。

最初の構造と今の構造、それぞれで学校の運営上で発生しそうな操作をしてみてください。
（例えば新しい講座を開講して生徒を追加するとか、転入生が来るとか、講座名が変わるとか…）

なんだか、テーブルを分けていると便利そうだなぁ、と思っていただけるかと思います。

ちなみに、履修の情報を管理しているテーブルには一意となる主キーがありませんね。
前半を読んでくれた人なら「おかしいぞ！」と思っていただけると思います。
しかし、これは学籍番号と講座番号、2つがセットとなって一意となる主キーの役割をしているので問題ないのです。
同じ学生が同じ講座を同時に履修することはないですからね。
こういうこともあるんだなぁと思っていてください。

こうやってテーブルを適切に分割することを「正規化」と言います。
適切に正規化されていることは「良いデータベース」になるための第一歩です。

JOIN活躍の気配

もうJOINが必要な理由は感じているんじゃないでしょうか。
いくらさっきの分割された状態が「良いデータベース」と言っても、3つに分かれたままでは知りたいことも知れません。

試しにJOINを使わないで山田くんの履修している授業を調べてみましょう。
（クエリ番号は後で使うので心の片隅に置いておいてください。）

クエリ1

select 学籍番号 from 学生情報 where 名前 == '山田';

-->  |学籍番号|
--   +-------+
--   |     2 |

学籍番号が分かったので履修情報を検索します。

クエリ2

select 講座番号 from 履修情報 where 学籍番号 == 2

-->  |講座番号|
--   +-------+
--   |     1 |
--   |     2 |

履修している講座の番号が分かったので、最後にその講座名を検索します。

クエリ3

select 名前 from 講座情報 where 講座番号 == 1 or 講座番号 == 2

-->  |    名前 |
--   +--------+
--   |   数学I |
--   | 数学III |

山田くんは「数学I」と「数学III」を履修していることがわかりました。
これだけを知るために3回もクエリを打たないといけないのは大変ですね。
しかも、「じゃあ佐藤くんは…」と言う時も再度3回のクエリを打たないといけません。

これは不便です、不便すぎます。
この操作を一括して代わりにやってくれるのがJOINです。

JOINしよう

そもそも、JOINとか言ってるから分かりにくいんです。
我々は日本人です。
日本語にしましょう。
JOINの日本語、それは結合です。

JOINはテーブルとテーブルを結合する！

分かりやすくないですか？
一つのテーブルだけじゃ情報が足りないから、違うテーブルを結合するんです。
適当に結合しても意味がないので、行のマッチングに必要な目印をONで指定するのです。

クエリを書く、その前に

先ほどのクエリ1とクエリ2でやったことを改めて確認してみましょう。
クエリ1では名前から学籍番号を取得して、クエリ2では学籍番号から履修情報を検索しましたね。

つまり、2つのテーブルで共通している情報（今回は学籍番号）を使って、2つのテーブルを連携して「山田くん」が履修している講座を調べたわけです。

JOINには共通する情報というのがとても大切です
共通する情報があれば異なる情報を関連づけられます

クエリを見てみよう

まず、学生ごとの履修講座番号を調べるクエリを日本語で考えてみましょう。
学生の名前と講座番号がわかれば良いですね。

--SELECT
    学生の名前 と 講座番号 が知りたいんですけど
--FROM
    学生情報テーブル だけじゃ情報が足りないので
--JOIN
    履修情報テーブル も確認してもらって
--ON
    両方の 学籍番号 が一致するように横に並べて一緒に見せてください

共通する情報(学籍番号)を使って名前と講座番号を紐づけていますね。
もう少しsqlっぽくにしてみましょう。

SELECT
    学生情報.名前, 履修情報.講座番号
FROM
    学生情報 -- の横に
JOIN
    履修情報 -- を表示してほしいんだけど
  ON
    学生情報.学籍番号 = 履修情報.学籍番号; -- これが一致するように並べてね

出力は次のようになるはずです。

名前	講座番号
田中	1
田中	3
山田	2
山田	3
佐藤	2
鈴木	1
鈴木	3

無事結合して情報が見られていますね！
JOIN成功です。

更に…

JOINは重ねがけができます。
クエリ1とクエリ2とクエリ3が一気にできるってことですね。
学生の名前と講座名が一度に分かる結果が返ってきます。

SELECT
    学生情報.学籍番号,
    学生情報.名前,
    講座情報.名前 AS 講座名
FROM 学生情報 -- の右横に
JOIN 履修情報 -- を
  ON 学生情報.学籍番号 = 履修情報.学籍番号 -- 両方の学籍番号が一致するように並べて、更にその右横に
JOIN 講座情報 -- を
  ON 履修情報.講座番号 = 講座情報.講座番号; -- 両方の講座番号が一致するように並べてね

学籍番号	名前	講座名
1	田中	数学I
1	田中	英語
2	山田	数学III
2	山田	英語
3	佐藤	数学III
4	鈴木	数学I
4	鈴木	英語

このクエリを見るとテーブル名.列名と記述する理由がわかると思います。
単に「名前」と書いただけでは学生情報テーブルの名前列なのか講座情報テーブルの名前列なのか分からないですよね。
これを区別するため、JOINを使うときはテーブルを明示して列名を指示します。

内と外

実は先ほどまでのJOINは内部結合というものです。
もう一つ、外部結合というものもあります。

これらはマッチングに失敗した行の扱いが異なります。

転入してきたばかりで履修登録をしていない中村さんを考えましょう。
学生情報テーブルだけ変更があり次のようになります。

学籍番号	名前	コース
1	田中	文系
2	山田	理系
3	佐藤	理系
4	鈴木	文系
5	中村	理系

学生ごとの履修講座番号を調べるクエリを実行してみます。

内部結合

SELECT
    学生情報.名前, 履修情報.講座番号
FROM
    学生情報 -- の横に
JOIN
    履修情報 -- を表示してほしいんだけど
  ON
    学生情報.学籍番号 = 履修情報.学籍番号; -- これが一致するように並べてね

結果は次のとおりです。

名前	講座番号
田中	1
田中	3
山田	2
山田	3
佐藤	2
鈴木	1
鈴木	3

中村さんが転入する前と変わりありませんね。

中村さんは履修登録をしていないので、中村さんの学籍番号「5」は履修情報テーブルの学籍番号列に存在していません。
SQLは中村の右横にも適切な講座番号を並べようとしますが、そんな行は履修情報テーブルに無いので諦めます。
諦めた結果、「表示しない！」となるのが内部結合です。

では外部結合はどうでしょうか。

外部結合

SELECT
    学生情報.名前, 履修情報.講座番号
FROM
    学生情報 -- の横に
LEFT JOIN   -- 左は絶対に残して
    履修情報 -- を並べてほしいんだけど
  ON
    学生情報.学籍番号 = 履修情報.学籍番号; -- これが一致するように並べてね
                                      -- もし一致する相手がなかったらNULLにしてね

結果は次のとおりです。

名前	講座番号
田中	1
田中	3
山田	2
山田	3
佐藤	2
鈴木	1
鈴木	3
中村	NULL

中村さんの講座はNULLになっていますね。

NULLとは「何もない」ことを表す特殊な型です。

先ほどの外部結合の指示は「LEFT JOIN」です。
左を絶対に残すという気合を感じますね。
中村の右横に並べる適切な講座番号が履修情報テーブルになかったとき、「左に結合」と言われているSQLは諦めて何も表示しないということができません。
左を残す、中村は表示するけど右に出すものがない、困った結果、何者でもないNULLを返すのです。

（右側を残すRIGHT JOINもあります。）

おまけ: アスタリスク（*）とは

アスタリスクの意味は「全て」です。

次のクエリは、「たくさんある列からhogeだけを選んで表示してね。」という指示です。

select hoge from table;

ここでアスタリスクを使うと次のようになります。

select * from table;

こうすると、id,name,age,email,hoge,hogehoge,etc.と全ての列が取得できます。
「何個あるか分からないけどとにかく全て」です。

SNSとかのログイン画面でパスワードを入力すると「*******」と表示されますよね。
あれも、英数字記号全て、を表しているのです。
全てを表す記号*で置き換えて、パスワードを誰に見られてもいいようにしているのです。

また、アスタリスクがSQLではCOUNT関数なんかでも使いますね。

select COUNT(*) from テーブル;

「テーブルにある全ての行を数える」という意味になります。
じゃあ、違う数え方があるのかというと…。

select count(列名) from テーブル;

これだとテーブルの「列名」がNULLでない行を数えてくれます。

アイテム	カテゴリ
テレビ	家電
扇風機	家電
ティッシュ	日用品
テーブル	家具
どこでもドア	NULL

count(*)だと5が返りますし、count(カテゴリ)だと4が返ります。
面白いですね。

まとめ

JOINは結合です。
複数のテーブルを横に並べると言ってもいいです。

そして、ONは結合のとき組み合わせに使う列を指定します。
右横に並べるとき、同じものを表す行が並ぶように目印にする列を指定できるのです。

皆様のご理解の一助になれば幸いです。
致命的な間違いがあったらどしどし指摘してください！

それでは、ごきげんよう。