SQL ゼロから始めるデータベース操作　第７章　結合

f:id:stonebeach-dakar:20160424222031p:plain

個人的にはRDBMSのキモである結合がようやく登場です。
（結合はよく知ってるのにサブクエリはあまり知らなかったんですよね。)

知っている事がほとんどですが、自分の知識の再確認のためにやっていきます。

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

作者: ミック
出版社/メーカー: 翔泳社
発売日: 2010/06/29
メディア: 大型本
購入: 39人クリック: 484回
この商品を含むブログ (16件) を見る

結合とは？
結合の種類
内部結合・外部結合の共通するルール
内部結合
外部結合
クロス結合
古い結合演算の書き方
第8章について

結合とは？

前回やった集合演算 UNIONはテーブルとテーブルを行方向へ連結するものでした。
結合は、テーブルとテーブルを列方向に連結するものという感じです。
イメージで言うと、こんな感じです。
f:id:stonebeach-dakar:20160512205435p:plain

本書では、

簡単に行ってしまうと、「別のテーブルから列を持ってきて列を増やす」操作

と紹介されています。

結合の種類

結合は3種類あり、

内部結合(INNER JOIN)
外部結合(OUTER JOIN)
クロス結合(CROSS JOIN)

の３つです。
本書では、クロス結合はほとんど使わないと紹介されていますが、自分も今まで実務で使ったことはありません。
ほとんど内部結合で要件に合わせて外部結合、という感じです。

内部結合・外部結合の共通するルール

結合するテーブルは一致する列が必ず必要です。

なぜかというと、この一致する列を基準に、データを設定するためです。
イメージで見ると、
f:id:stonebeach-dakar:20160512205912p:plain

もし、2つ目のテーブルにID列がなかったら、2つのテーブルを結合しても、
どの1つ目のテーブルのレコードと、2つ目のテーブルのレコードを連結させればいいのか
わかりませんよね。

なので、共通するID列を準備してあげることで、同じIDの値同士で結合できる、ということです。
いわゆる橋渡しの役目ですね。
ちなみに、総当りなクロス結合は、共通する列は必要ありません。

内部結合

内部結合は、先ほどのイメージ
f:id:stonebeach-dakar:20160512205435p:plain これそのまま、というものです。

結合する2つのテーブルで、同じIDが両方にあるレコード同士を結合します。
片方にしかIDががないものは結合されません。

実際にSQL文を叩いてみます。
まず、2つのテーブルを確認します。

mysql> SELECT * FROM User1;
+----+------+-----------+------+
| id | Name | Address   | Age  |
+----+------+-----------+------+
|  1 | Bell | Seattle   |   24 |
|  2 | Jack | Cupertino |   27 |
|  3 | Mor  | San Diego |   19 |
|  4 | Mike | New York  |   38 |
+----+------+-----------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM Skill;
+----+--------+-------------+
| id | Skill  | Favorite    |
+----+--------+-------------+
|  1 | Web    | RubyOnRails |
|  2 | Destop | C#          |
|  3 | DesTop | Java        |
|  4 | Web    | PHP         |
|  5 | Web    | JQuery      |
+----+--------+-------------+
5 rows in set (0.00 sec)

さて、結合してみます。

mysql> SELECT *
    -> FROM User1 INNER JOIN Skill ON User1.id = Skill.id;
+----+------+-----------+------+----+--------+-------------+
| id | Name | Address   | Age  | id | Skill  | Favorite    |
+----+------+-----------+------+----+--------+-------------+
|  1 | Bell | Seattle   |   24 |  1 | Web    | RubyOnRails |
|  2 | Jack | Cupertino |   27 |  2 | Destop | C#          |
|  3 | Mor  | San Diego |   19 |  3 | DesTop | Java        |
|  4 | Mike | New York  |   38 |  4 | Web    | PHP         |
+----+------+-----------+------+----+--------+-------------+
4 rows in set (0.00 sec)

これで完全にイメージどおりの結果になりました。
橋渡しの役目を行ってるのが、ON句で、idを連結しています。

外部結合

外部結合は、内部結合と動きとしてはほとんど変わりません。
違いは１つだけ、「片方にしかないデータも結合される」ということです。

実際に動きを見てみると、

mysql> SELECT *
    -> FROM User1 RIGHT OUTER JOIN Skill ON User1.id = Skill.id;
+------+------+-----------+------+----+--------+-------------+
| id   | Name | Address   | Age  | id | Skill  | Favorite    |
+------+------+-----------+------+----+--------+-------------+
|    1 | Bell | Seattle   |   24 |  1 | Web    | RubyOnRails |
|    2 | Jack | Cupertino |   27 |  2 | Destop | C#          |
|    3 | Mor  | San Diego |   19 |  3 | DesTop | Java        |
|    4 | Mike | New York  |   38 |  4 | Web    | PHP         |
| NULL | NULL | NULL      | NULL |  5 | Web    | JQuery      |
+------+------+-----------+------+----+--------+-------------+
5 rows in set (0.00 sec)

内部結合ではなかったSkillテーブルのid:5のデータが表示されます。
ただし、User1テーブルにはid:5がないので、NULLと表示されてしまいます。
イメージでいえば、こんな感じです。
f:id:stonebeach-dakar:20160512211912p:plain

本書では、

元のテーブルにない（つまりテーブルの外部から)情報を結果に持ってくるという意味で、「外部」結合と呼ばれるわけです。

と、書かれています。ここで言えば、元のUser１テーブルになかったID:5のデータが外部から来たわけですね。

外部結合の文にあるRIGHTと言うのは右側をマスタとするという意味です。
もちろん、LEFT つまり左側をマスタにするということもできます。

実際に左側をマスタにしてみると、

mysql> SELECT *
    -> FROM User1 LEFT OUTER JOIN Skill ON User1.id = Skill.id;
+----+------+-----------+------+------+--------+-------------+
| id | Name | Address   | Age  | id   | Skill  | Favorite    |
+----+------+-----------+------+------+--------+-------------+
|  1 | Bell | Seattle   |   24 |    1 | Web    | RubyOnRails |
|  2 | Jack | Cupertino |   27 |    2 | Destop | C#          |
|  3 | Mor  | San Diego |   19 |    3 | DesTop | Java        |
|  4 | Mike | New York  |   38 |    4 | Web    | PHP         |
+----+------+-----------+------+------+--------+-------------+
4 rows in set (0.00 sec)

なんと！id:5が消えてしまった!(DQ風)

なぜこうなるかというと、マスタ側のテーブルのデータを全部出力するのが外部結合の動作です。
なので、今回マスタ側になっているUser1テーブルにはid:5のデータはないので、出てこないわけです。
先程は、Skillテーブルがマスタになっていたので出てきたわけですね。

もし、LEFTを使ってSkillテーブルにあるid:5のデータを表示したい、と思った時は
左側にSkillテーブルを置けばいいわけです。
実際にやってみると、

mysql> SELECT *
    -> FROM Skill LEFT OUTER JOIN User1 ON Skill.id = User1.id;
+----+--------+-------------+------+------+-----------+------+
| id | Skill  | Favorite    | id   | Name | Address   | Age  |
+----+--------+-------------+------+------+-----------+------+
|  1 | Web    | RubyOnRails |    1 | Bell | Seattle   |   24 |
|  2 | Destop | C#          |    2 | Jack | Cupertino |   27 |
|  3 | DesTop | Java        |    3 | Mor  | San Diego |   19 |
|  4 | Web    | PHP         |    4 | Mike | New York  |   38 |
|  5 | Web    | JQuery      | NULL | NULL | NULL      | NULL |
+----+--------+-------------+------+------+-----------+------+
5 rows in set (0.00 sec)

となります。

クロス結合

最後のクロス結合は、先程も書いたように実務ではほとんど使わないものです。
それにもかかわらず、紹介されている理由が本書に書かれています。

クロス結合が、すべての結合演算の基礎だからです。

基礎。基礎は大切。

クロス結合という名前だけですでに予想着くかもしれませんが、やってみましょう。

mysql> SELECT *
    -> FROM User1 CROSS JOIN Skill;
+----+------+-----------+------+----+--------+-------------+
| id | Name | Address   | Age  | id | Skill  | Favorite    |
+----+------+-----------+------+----+--------+-------------+
|  1 | Bell | Seattle   |   24 |  1 | Web    | RubyOnRails |
|  2 | Jack | Cupertino |   27 |  1 | Web    | RubyOnRails |
|  3 | Mor  | San Diego |   19 |  1 | Web    | RubyOnRails |
|  4 | Mike | New York  |   38 |  1 | Web    | RubyOnRails |
|  1 | Bell | Seattle   |   24 |  2 | Destop | C#          |
|  2 | Jack | Cupertino |   27 |  2 | Destop | C#          |
|  3 | Mor  | San Diego |   19 |  2 | Destop | C#          |
|  4 | Mike | New York  |   38 |  2 | Destop | C#          |
|  1 | Bell | Seattle   |   24 |  3 | DesTop | Java        |
|  2 | Jack | Cupertino |   27 |  3 | DesTop | Java        |
|  3 | Mor  | San Diego |   19 |  3 | DesTop | Java        |
|  4 | Mike | New York  |   38 |  3 | DesTop | Java        |
|  1 | Bell | Seattle   |   24 |  4 | Web    | PHP         |
|  2 | Jack | Cupertino |   27 |  4 | Web    | PHP         |
|  3 | Mor  | San Diego |   19 |  4 | Web    | PHP         |
|  4 | Mike | New York  |   38 |  4 | Web    | PHP         |
|  1 | Bell | Seattle   |   24 |  5 | Web    | JQuery      |
|  2 | Jack | Cupertino |   27 |  5 | Web    | JQuery      |
|  3 | Mor  | San Diego |   19 |  5 | Web    | JQuery      |
|  4 | Mike | New York  |   38 |  5 | Web    | JQuery      |
+----+------+-----------+------+----+--------+-------------+
20 rows in set (0.00 sec)

長い!
4行のデータがあるテーブルと５行のデータがあるテーブルのすべての組み合わせなので、
4 * 5で20行になります。

実際に、この中で2つのidが同じものを抽出すると、

mysql> SELECT *
    -> FROM User1 CROSS JOIN Skill
    -> WHERE User1.id = Skill.id;
+----+------+-----------+------+----+--------+-------------+
| id | Name | Address   | Age  | id | Skill  | Favorite    |
+----+------+-----------+------+----+--------+-------------+
|  1 | Bell | Seattle   |   24 |  1 | Web    | RubyOnRails |
|  2 | Jack | Cupertino |   27 |  2 | Destop | C#          |
|  3 | Mor  | San Diego |   19 |  3 | DesTop | Java        |
|  4 | Mike | New York  |   38 |  4 | Web    | PHP         |
+----+------+-----------+------+----+--------+-------------+
4 rows in set (0.00 sec)

内部結合と同じデータになります。

なので、結合演算の基礎というわけですね。

古い結合演算の書き方

本書では、古い結合演算の構文として、JOIN 句を使わずにWHERE句を使う方法が紹介されています。
今回のサンプルで言えば、以下の様な感じです。

mysql> SELECT *
    -> FROM User1, Skill
    -> WHERE USer1.id = Skill.id;
+----+------+-----------+------+----+--------+-------------+
| id | Name | Address   | Age  | id | Skill  | Favorite    |
+----+------+-----------+------+----+--------+-------------+
|  1 | Bell | Seattle   |   24 |  1 | Web    | RubyOnRails |
|  2 | Jack | Cupertino |   27 |  2 | Destop | C#          |
|  3 | Mor  | San Diego |   19 |  3 | DesTop | Java        |
|  4 | Mike | New York  |   38 |  4 | Web    | PHP         |
+----+------+-----------+------+----+--------+-------------+
4 rows in set (0.00 sec)

このような書き方は、デメリットが多くあるため使わないように、結合はJOINを使うこと、
と紹介されていました。
(デメリットについては本書を読むのがわかりやすいです。)

JOIN句は新しい書き方(と言っても出てから３０年近く立ちますが)なので、昔のエンジニアの方が書いた
SQL文は、WHEREで結合しているものが多い、という事情があるようです。
(JOINが定義されたのがSQL92あたり、っぽい。)

okwave.jp

第8章について

第8章はWindow関数(通称、窓関数)が紹介されます。
が、この窓関数、MySQLは対応していません。

対応していません
対応していません
対応していません

MySQLは現在Oracleが開発していることもあって今後窓関数の対応があるか、は怪しい、というのがネットではもっぱらの評判です。
(OracleDBとの差別化という意味もあって)
MySQL派生のMariaDBも窓関数に対応していない事を考えると、
あくまでDBMSとしての性能や堅牢性を上げる方向にシフトしていくのかなー、なんて思ってます。