第13回　実践的検索操作を学ぼう（4） - MySQL講座

はじめに

みなさんこんにちは。サカイです。前回まで３回に渡って抽出文の基礎を学んで来ました。これまではすべて１つのテーブルからのSELECTでしたが、リレーショナルデータベース管理システム(RDBMS)では複数のテーブルから必要な情報を一度に取り出す仕組みこそが、抽出の醍醐味でもあります。この、複数テーブルからデータを取り出すためのテクニックが「結合」です。

今回学ぶこの「結合」。少し難しいかもしれませんが、例や考え方などを多く紹介していきますのでぜひ挑戦してみてください。

結合するぞ結合するぞ結合するぞ！

• せんぱぁい。今日はいよいよ「結合」を教えてくれるんですよね！
• おっ！ノッケからやる気満々だね。
• だってぇ先輩、「結合は後で教えるからね」と言って何週間も　じらしたじゃないですかあ～。
• ごめんごめん。・・・どうでも良いけどその、くねくねしながら話すのは、やめてくれるかな(笑)。
• あはは。楽しみにしていた気持ちをカラダ全体で表現してみました。
• さて、それじゃ説明しようか。ちょっと話が長くなるけれども最初に結合とは何かということについて説明するね。
• はい。
• 単純に「結合」と言っているけど、これは「２つ以上の "テーブルを" 結合する」ことを指している。１つのテーブル上には存在しないカラムの値を参照したい場合に使うんだ。

これまで太郎君は１つのテーブルからの抽出操作しか知らなかったから、抽出結果をもとにしてもう一度手作業で別のテーブルからの検索を行ったりしたことが何度もあったよね。あれを１つのSQL文で書けるようになると思ったらいいよ。結合によって、複数のテーブルを仮想的に１つのテーブルのように扱えると考えてもいい。

といっても、よくわからないよね。具体例を見てもらった方がイメージがわくと思うから、２、３個例を出そうか。

顧客情報 with 都道府県名

• ずっと昔(第10回)に、ある顧客の情報を検索して、続けてその顧客の都道府県名を検索してもらったのを覚えているかな。
• やったような、やってないような。。いろんな事を覚えたのですいません、ちょっと忘却の彼方かもしれません。。
• そうだろうね(笑)。改めて当時やったことをおさらいしておくと、「customer の id が 999 の人」を検索した後、検索結果の都道府県コード(prefecture_code)が数字だと何県か分からないので都道府県名を調べて欲しい、という課題だった。

以下のように customers テーブルへの SELECT 文を実行した後、 prefecure_codeが27 であることを目で確認して、２つめの SELECT 文を実行したんだったね。

mysql> SELECT * FROM customers WHERE id=999;
+-----+-----------+----------+-----------------+---------------------+------------+---------------------+---------------+
| id  | name      | zip      | prefecture_code | address             | birthday  | regist_date         | unregist_date |
+-----+-----------+----------+-----------------+---------------------+-----------+---------------------+---------------+
| 999 | 原田 大我 | 708-0119 | 27              | どこかの住所17-2-59 | 1939-12-24 | 2005-04-20 00:00:00 | NULL          |
+-----+-----------+----------+-----------------+---------------------+------------+---------------------+---------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM prefecture WHERE code=27;
+------+--------+
| code | name   |
+------+--------+
| 27   | 大阪府 |
+------+--------+
1 row in set (0.02 sec)

• 思い出しました。あのころは自分が打ったコマンドでデータが表示されることだけでわくわくしていました。今は、なんでこんなに面倒なんだろう、もっとラクな方法はないのかな、と思うことも増えてきましたけど。
• ラクするために考えて、工夫する姿勢はとってもいいね。幸いにもこの２つのSQL文を１回で済ませる方法があるんだ。「結合」だね。慣れないうちは大変に思えるかもしれないけど慣れるととても便利に使えるよ。まずこの結合をするために、もう一度テーブルレイアウトを確認しておこう。

やりたいことを箇条書きにすると

となる。４つめは「結合条件」とも呼ばれるんだ。

• 正直、分かったような分からないような漠然とした感じです・・・・。
• SQL文を見てもらおう。

1: SELECT customers.id, customers.name, customers.zip, customers.prefecture_code, 
2:        prefecture.name, customers.address, customers.birthday, customers.regist_date 
3:   FROM customers, prefecture
4:  WHERE customers.prefecture_code=prefecture.code
5:    AND customers.id=999;

こんな感じになる。先頭の数字は行番号ね。

まず ２つのテーブルから取ってくる(i)は 3行目 の FROM に２つのテーブル名を書くことで表現できる
(ii)の抽出条件の指定は 5行目 の WHERE 条件内(条件は AND でつなげるんだったよね)。
(iii) は 1,2 行目でカラムの名前を羅列している部分だ。複数のテーブルからデータを抽出する時には、どちらのテーブルのカラムかを明示する必要があるんだ。「.」(ドット)でつなげるんだよ。(*1)
そして (iv) の結合条件は 4行目 だ。２つのテーブルで値を位置させたいカラム(結合カラム)を 4行目 のように指定するんだ。
結合についてはこのように WHERE 条件で結合させるほか、FROM 内で JOIN 句を使う方法もあるけれども一気に両方覚えようとすると混乱するだろうから、まずはこの表現方法を自分のものとしてマスターするのがいいと思う。

• 一気に複雑になりましたね。
• テーブル名を 「.」 の前に修飾しているから全体が長くなって複雑になったように見えるせいもあるかもしれないね。とりあえず SELECT の後ろ、FROM の前までのカラム名の羅列は気にせず見ないようにすると本質が見えるんじゃないかな。
• あ。本当だ。意外とシンプルな気がします。

SELECT (欲しいカラム)
  FROM (抽出元のテーブル(複数)
 WHERE (結合条件)
   AND (検索条件)

こういう構造になっているんですね。

• その通り。では実際にこのクエリを実行してみようか。

mysql> SELECT customers.id, customers.name, customers.zip, customers.prefecture_code, prefecture.name, customers.address, customers.birthday, customers.regist_date
    ->   FROM customers, prefecture
    ->  WHERE customers.prefecture_code=prefecture.code
    ->    AND customers.id=999;
+-----+-----------+----------+-----------------+--------+---------------------+------------+---------------------+
| id  | name      | zip      | prefecture_code | name   | address             | birthday   | regist_date         |
+-----+-----------+----------+-----------------+--------+---------------------+------------+---------------------+
| 999 | 原田 大我 | 708-0119 | 27              | 大阪府 | どこかの住所17-2-59 | 1939-12-24 | 2005-04-20 00:00:00 |
+-----+-----------+----------+-----------------+--------+---------------------+------------+---------------------+
1 row in set (0.03 sec)

• customers だけからの抽出では分からなかった「大阪府」が表示されていますね。１回のクエリで済むのは嬉しいのですが、最初に２回のクエリに分けて実行していたのに比べて格段にクエリが長くなっていますよね。。なんとかならないんですか？
• 実は、FROM に記述したテーブル名に別名を指定することができるんだ。以下の例では customers テーブルに c という短縮名を、prefecture テーブルには p という短縮名を設定したことで、抽出カラムや WHERE 条件内でこの短縮名を使って表すことができる。たまたまこの例では短縮名は両方とも１文字にしたけど、１文字である必要はないよ。こんな感じ。

SELECT c.id, c.name, c.zip, c.prefecture_code, p.name, c.address, c.birthday, c.regist_date 
  FROM customers c, prefecture p
 WHERE c.prefecture_code=p.code
   AND c.id=999;

• 話を続けるけど、先ほどのクエリ実行結果をもう一度見て欲しい。カラム名に関して何か気づくことがあるかな？
• えぇと。。。あっ、name という名前のカラムが２つあります。ひとつは顧客の名前で、もう一つは都道府県の名前ですね。
• その通り。どうしたらいいと思う？
• 別名、、、、かな。。。
• 正解。カラム名の後ろに空白をあけて別名を指定できるんだったね。(第10回参照)
• これは僕にやらせてください！今日は先輩がやってばっかりですから(笑)。

mysql> SELECT c.id, c.name, c.zip, c.prefecture_code, p.name prefecture_name, c.address, c.birthday, c.regist_date
    ->   FROM customers c, prefecture p
    ->  WHERE c.prefecture_code=p.code
    ->    AND c.id=999;
+-----+-----------+----------+-----------------+-----------------+---------------------+------------+---------------------+
| id  | name      | zip      | prefecture_code | prefecture_name | address             | birthday   | regist_date         |
+-----+-----------+----------+-----------------+-----------------+---------------------+------------+---------------------+
| 999 | 原田 大我 | 708-0119 | 27              | 大阪府          | どこかの住所17-2-59 | 1939-12-24 | 2005-04-20 00:00:00 |
+-----+-----------+----------+-----------------+-----------------+---------------------+------------+---------------------+
1 row in set (0.00 sec)

• うん、いいね。

---
(*1) そのカラム名がどちらか一方のテーブルにしか存在しない場合はMySQLが良きに計らってくれますが、仕様変更などが発生した際に同じ名前のカラムができてしまう可能性など将来への安定性のためにも、テーブル名を明示する習慣をつけることをお勧めします。(本文へ)