【PHP講座17】SQL編4 -テーブルの結合-

前回は、ある列が同じデータを一つのグループにまとめるGROUP BY句を解説した。

また、その際によく利用する集合関数についても軽く触れた。

ちょっと難しかったかもしれないが、よく使うものの一つなのでしっかり覚えておこう。

以下がその記事だ。

【PHP講座16】SQL編3 -グループ化- | Shino’s Mind Archive

さて、これまで解説してきた内容は、どれも一つのテーブルに対する操作だった。

今回は、それを複数に拡張しよう。

そのために必要な、テーブルの結合という操作を解説する。

なぜそんなことをする必要があるのかから解説をしていくので、必要性とともに理解していってほしい。

テーブルの結合とは

さて、これまで紹介してきたサンプルのテーブルは、実を言うとあまり良くない形も含まれていた。

例えば、店舗の売り上げを管理するstoreテーブル。

以下に再掲しよう。

これの何がよろしくないかというと、店舗に関する不変的な情報と、状況によって変化する情報が同時に管理されてしまっている。

エリアに関する情報は、その店舗が引っ越しでもしない限り変わることがない。

基本的には、変わらない情報だ。

それに対し、売り上げはその期間…例えば月ごとに集計していれば、毎月変化してしまう。

これは、切り離して管理した方がいいだろう。

というわけで、通常は正規化というものを行うことによりテーブルを複数に分割していく。

これを解説しだすとまた非常に長くなってしまうので、いったんはそういう手法があるんだなぁという認識でいよう。

で、テーブルを分割するということは、複数のテーブルが紐づいて管理されるということ。

つまり、複数のテーブルから情報を取得し、最終的な結果を得るということが必要になってくる。

今回解説する内容は、その複数テーブルの結合だ。

この結合にも幾つか種類があるのだが、今回は一番基本となる内部結合について解説しよう。

内部結合

結合とは、簡単に言ってしまうと複数のテーブルで同じものを表す列について、それが同じデータ同士でくっつけてしまう操作のこと。

言葉で説明してもなんのこっちゃという感じなので、具体例で説明していこう。

まず、以下二つのテーブルがあったとしよう。

イメージとしては、ショッピングサイトのユーザー情報とカート情報だ。

…本来であれば、別で商品テーブルを用意し、そこで商品情報を管理すべきなのだが、今回は説明を簡単にするためあえてこの形にしている。

さて、結合でできることだが、例えばユーザー名と、そのユーザーがカートに入れている商品名を一覧で表示する、といったことができる。

ユーザー名はuserテーブルで、カートの商品情報はcartテーブルで管理されているので、ここで結合が使えるというわけだ。

あくまでイメージだが、以下のような表を作り出すと思ってもらえればいいだろう。

まず、大前提としてcartテーブルのuser_id列は、userテーブルのuser_id列と同じものを表していることに注目しよう。

これらの列で、同じものを一つのデータとして見なせば、ユーザー名とカート情報を一度に表示できそうだ。

では、それを行うための構文を。

SELECT テーブル名.列名 FROM テーブル名1 INNER JOIN テーブル名2 ON テーブル名1.列名 = テーブル名2.列名;

通常のテーブル名の後ろに、INNER JOINというものがくっついている。

これが、複数のテーブルを紐づけるためのものだ。

この直後にもう一つのテーブル名を書き、さらにONとつなげる。

その後ろでは、二つのテーブルで結合するための行を指定してあげる。

両方とも、テーブル名と列名をドットで挟んだ形で指定してあげる必要がある。

また、SELECT直後の列名も、両方に同じ列名が存在するとどちらか分からなくなってしまうので、テーブル名.列名という書き方にしてあげよう。

では、具体例を。

上で軽く書いた、ユーザー名とカート内の商品名を一覧で出してみよう。

…と、先にサンプルテーブルの作成から。

今回も長いので、SQLで簡単に作ってしまおう。

以下4つの塊を、上から順番に実行してもらえれば完了だ。

まずはuserテーブルの作成。

CREATE TABLE `testdb`.`user` ( `user_id` INT NOT NULL AUTO_INCREMENT COMMENT '通しID' , `user_name` VARCHAR(100) NOT NULL COMMENT 'ユーザー名' , `password` TEXT NOT NULL COMMENT 'パスワード' , PRIMARY KEY (`user_id`)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci COMMENT = 'ユーザー情報管理テーブル';

次に、userテーブルへのデータ挿入。

INSERT INTO `user` (`user_id`, `user_name`, `password`) VALUES (NULL, 'user1', 'pass1');
INSERT INTO `user` (`user_id`, `user_name`, `password`) VALUES (NULL, 'user2', 'pass2');
INSERT INTO `user` (`user_id`, `user_name`, `password`) VALUES (NULL, 'user3', 'pass3');
INSERT INTO `user` (`user_id`, `user_name`, `password`) VALUES (NULL, 'user4', 'pass4');
INSERT INTO `user` (`user_id`, `user_name`, `password`) VALUES (NULL, 'user5', 'pass5');

そして、cartテーブルの作成。

CREATE TABLE `testdb`.`cart` ( `cart_id` INT NOT NULL AUTO_INCREMENT COMMENT '通しID' , `user_id` INT NOT NULL COMMENT 'ユーザーID' , `product_name` VARCHAR(100) NOT NULL COMMENT '商品名' , `product_price` INT NOT NULL COMMENT '商品値段' , `num` INT NOT NULL COMMENT '数量' , PRIMARY KEY (`cart_id`)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci COMMENT = 'カート情報管理テーブル';

最後に、cartテーブルへのデータ挿入だ。

INSERT INTO `cart` (`cart_id`, `user_id`, `product_name`, `product_price`, `num`) VALUES (NULL, '1', 'お茶', '120', '1');
INSERT INTO `cart` (`cart_id`, `user_id`, `product_name`, `product_price`, `num`) VALUES (NULL, '1', '水', '90', '2');
INSERT INTO `cart` (`cart_id`, `user_id`, `product_name`, `product_price`, `num`) VALUES (NULL, '2', 'お茶', '120', '3');
INSERT INTO `cart` (`cart_id`, `user_id`, `product_name`, `product_price`, `num`) VALUES (NULL, '3', 'コーラ', '130', '1');
INSERT INTO `cart` (`cart_id`, `user_id`, `product_name`, `product_price`, `num`) VALUES (NULL, '3', 'お茶', '120', '1');
INSERT INTO `cart` (`cart_id`, `user_id`, `product_name`, `product_price`, `num`) VALUES (NULL, '3', 'オレンジジュース', '120', '2');
INSERT INTO `cart` (`cart_id`, `user_id`, `product_name`, `product_price`, `num`) VALUES (NULL, '4', 'コーラ', '130', '2');
INSERT INTO `cart` (`cart_id`, `user_id`, `product_name`, `product_price`, `num`) VALUES (NULL, '5', 'オレンジジュース', '120', '4');

これで、上に出した例通りのテーブルとデータができた。

これに対し、ユーザー名とカートの商品名を並べて表示してみよう。

SELECT user.user_name, cart.product_name FROM user INNER JOIN cart ON user.user_id = cart.user_id;

これで実行すると…

このような結果になる。

これで、userテーブルとcartテーブルの結合ができた、というわけだ。

なお、今回解説した内部結合(INNER JOIN)は、結合する列両方に含まれるデータのみで結合を行う。

そのため、例えばユーザーに6番目のデータが入っていたとしても、cartにはそのユーザーの情報が入っていないので、結果には表示されない。

これも表示するのが、外部結合と呼ばれるもの。

これにもさらに内訳があるが、そこまで解説すると長くなってしまうので、また今度にしよう。

他の句との組み合わせ

もちろんだが、これまで解説してきたWHERE句、GROUP BY句などとも併用可能だ。

例えば、GROUP BY句と組み合わせる場合は以下のような形になる。

SELECT テーブル名.列名 FROM テーブル名1 INNER JOIN テーブル名2 ON テーブル名1.列名 = テーブル名2.列名 GROUP BY テーブル名.列名;

処理の流れは、今回の結合が最初に行われるので、WHERE句などもその後に処理がされる。

つまり、どちらのテーブルの列に対しても絞り込み、グループ化などが可能なので、覚えておくといい。

参考までに、各ユーザーのカートに入っている商品個数の合計を出すSQLを載せておこう。

SELECT user.user_name, SUM(cart.num) FROM user INNER JOIN cart ON user.user_id = cart.user_id GROUP BY user.user_name;

これを実行すると、以下の結果が返ってくる。

詳細は解説しないので、なぜこのような結果になるか考えてみよう。

これが分かれば、今回の内容は問題ないはずだ。

おわりに

今回は、複数のテーブルを結びつける結合というものを解説した。

かなり複雑で、SQLも長くなってくるが、どこで何をしているか、しっかり確認しながら進めていこう。

さて、さらにSELECT文について踏み込んでもいいのだが、一旦これで大まかなSELECT文の解説は終わりにしよう。

次回は、ついにSELECT文から離れ、別のSQLに進んでいく。

まずは、新たなデータを追加するINSERT文を解説しよう。

今回までの内容は後でPHPからMySQLにアクセスするときにまた出てくるので、それまでに身に付けておこう。