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

購入明細 with 商品名と価額

• もうひとつやってみようか。これも過去に２度３度の手間を掛けて２つのテーブルからの情報を取り出していたものを結合により１回でできるようになる例だ。
• これも以前にやったクエリを再度実行してみるけど、購入ヘッダIDが 6294 のものの明細を取得し、そのそれぞれについて商品情報を手作業で検索したんだったね。

mysql>SELECT * FROM kounyuu_m WHERE kounyuu_h_id=6294;
+-------+--------------+---------+--------+-------+
| id    | kounyuu_h_id | item_id | amount | price |
+-------+--------------+---------+--------+-------+
| 24426 |         6294 |     309 |      1 |  9770 |
| 24427 |         6294 |     170 |      5 | 10960 |
| 24428 |         6294 |      20 |      5 |  7610 |
| 24429 |         6294 |     179 |      1 |  4200 |
+-------+--------------+---------+--------+-------+

mysql>SELECT * FROM items WHERE id=309;
+-----+--------+------------+
| id  | name   | base_price |
+-----+--------+------------+
| 309 | 助六74 |       9770 |
+-----+--------+------------+

mysql>SELECT * FROM items WHERE id=170;
：(略)

• 思い出しました。これ、購入明細から取ったあと４回も実行して面倒だなって思ったんですよ。 IN 句を教えてもらっても、結局検索したいIDを自分で並べて書かなきゃいけなかったし。
• これを結合を使って１回のクエリにできるかな？ ちょっと用事があって１０分くらい離れるからその間に考えてごらん。
• はい、頑張ってみます。

　　　　　　　　　　・

　　　　　　　　　　・

　　　　　　　　　　・

• name カラムがこれも２つあるから、、、出力結果がわかりやすいように商品名のほうには別名をつけて。。。
• お待たせ。できたかな？
• あっ、先輩イイカンジでできていますよ。

mysql> SELECT km.*, i.name item_name, i.base_price
    ->   FROM kounyuu_m km, items i
    ->  WHERE km.item_id=i.id
    ->    AND km.kounyuu_h_id=6294;
+-------+--------------+---------+--------+-------+----------------+------------+
| id    | kounyuu_h_id | item_id | amount | price | item_name      | base_price |
+-------+--------------+---------+--------+-------+----------------+------------+
| 24426 |         6294 |     309 |      1 |  9770 | 助六74         |       9770 |
| 24427 |         6294 |     170 |      5 | 10960 | 超90           |      10960 |
| 24428 |         6294 |      20 |      5 |  7610 | 雪姫15         |       7610 |
| 24429 |         6294 |     179 |      1 |  4200 | フロンティア36 |       4200 |
+-------+--------------+---------+--------+-------+----------------+------------+
4 rows in set (0.05 sec)

• 購入明細テーブルの全カラムが欲しいので、いちいち全部のカラムを並べて書くのも面倒だったから「km.*」という書き方にしてみたらそれっぽく動作したんですけど、こんな書き方でいいんですか？
• いろいろ試してみるのは良いことだね。その書き方でOK。
  少し説明しておくと、１つのテーブルから抽出する時にはテーブル名を修飾する必要がなかったから、 SELECT * のように特にテーブル名を明示することなく * だけを指定したよね。

これも実はテーブル名を修飾しても構わないんだ。

 SELECT * FROM kounyuu_m; 

の代わりに

 SELECT kounyuu_m.* FROM kounyuu_m; 

と書いても

 SELECT km.* FROM kounyuu_m km; 

と書いても同じということだね。(実際にこのクエリを実行して試してみる時には LIMIT 5 などをつけて少件数のデータだけが表示されるようにしたほうがいいけど。そうでないと大量のデータが画面に表示されます。)
だから、太郎君が想像して書いた km.* というのは km テーブルの全カラムという意味でこの例とまったく一緒だということが分かるだろう？

• なるほど。

先輩の結合クエリの考え方

• ところで先輩、さっきの課題も随分と悩んだんですけど、先輩だったらちょちょいとあっという間にSQL文を作れちゃうんですよね。どうやって考えているのか教えてもらってもいいですか。
• 思考回路は人それぞれだから参考になるかどうかわからないけど、僕の場合は・・・

(1) まずテーブルレイアウトの図とにらめっこする。登場人物となるテーブルを理解する。
テーブル間の関係(結合条件)も一緒に。
(2) 以下のようなフォーマットがアタマの中にあって、このそれぞれを順に埋めていく。

SELECT
  FROM 
 WHERE
   AND
:
ORDER BY / GROUP BY / LIMIT など

(2-1) まず登場人物となるテーブルをFROMに書いちゃう

SELECT
  FROM kounyuu_m km, items i
 WHERE
   AND

(2-2) テーブルが複数ある場合は結合条件を書いちゃう

SELECT
  FROM kounyuu_m km, items i
 WHERE km.item_id=i.id
   AND

(2-3) 明確な抽出条件(ここでは明細IDが6294)があれば書いちゃう

SELECT
  FROM kounyuu_m km, items i
 WHERE km.item_id=i.id
   AND km.kounyuu_h_id=6294

(2-4) 出力したいカラム名をテーブルレイアウトを見ながら羅列する

SELECT km.*, i.name item_name, i.base_price
  FROM kounyuu_m km, items i
 WHERE km.item_id=i.id
   AND km.kounyuu_h_id=6294

(2-5) その他 件数が多い場合の LIMIT や、表示順 ORDER BY などの指定をしたければする

(今回の例では、なし)

というように考えることが多いかな。今回は２テーブルの例ばかりだったけれど、より多くのテーブルを一気に結合する場合でも基本的にこの順序、カラム一覧は最後にだらだら書くことが多いね。ただし、グループ関数を使った抽出とかの場合は必要に応じて早い段階でカラム名一覧を記述することもあるかな。

• うわぁ、本邦初公開！先輩のアタマん中！　って感じですね(笑)。
  前から順番に考えているわけじゃないんだぁ。実はさっきは、いきなり SELECT のすぐ後ろのカラム一覧を書こうとして手が止まっていたんですよ。
• それは難しそうだね。僕も常々、「どこから取ってくるか(FROM)決まっていないのに、取ってくるカラムなんて決められない」と思っているんだよ。余談になるけれどももしSQL文の構文を変えていいのならば、

 SELECT FROM (テーブル名) WHERE (条件) COLUMNS (出力カラム名) 

のようにしたいなぁ。

• 先輩オリジナルの構文ですね。Senpai Query Language で SQL と。。

もっとやってみよう

• そのほか、今までのレッスンの中でも結合を使うと便利そうだなと思える例がいっぱいあったよね。
• はい。いっぱいじらされました(笑)
• それらの検索を結合を使ってやってみたいと思うよね。
• もちろんです！
• じゃぁ、自分で色々試してみてくれるかな。基本的にほとんど今回説明した考えかたでできると思うんだ。
• う。。しまった。うまくハメられたような気が・・・(苦笑)。もちろん、やってみますよ！「本邦初公開！先輩のアタマん中！」を伝授してもらったのでできると思います！
• その意気その意気。じゃ今日はここまでにしよう。
• どうもありがとうございました！

おわりに

みなさん、どうでしたか。初めての２テーブル結合してのクエリ。先輩の思考回路大公開はわかりやすかったですね。クエリを前から書こうとしないでいいという発想は、今まで結合文がうまく書けなかった人にも何らかのヒントになったのではないでしょうか。

次回以降、データの更新やテーブルレイアウトの変更、そして日常の業務の中で発生する様々な細かいノウハウを紹介していきます。お楽しみに。