未経験・初心者向け「SQL鬼練１１〜INNER JOIN句を使ったテーブル同士の内部結合〜」

ここでは、初心者から実践的に学べるプログラミングスクール「ウェブカツ!!」のPHP・MySQL部入門またはWEBサービス部を受講している生徒の方向けに、さらに「SQL」についての理解度を上げるため特訓をしていきます。

ここからは、WEBサービス部で初めて出てくる内容を元に特訓をするので、まだWEBサービス部を１周見ていない人は見てからにしてください。

出題の回答は全て「SQL言語」を使って答えてください。

DBソフトの種類によってSQL言語は微妙に違いますが（と言っても基本の書き方はどれも同じなのでどのDBでも使える知識です）、ここでは「MySQL」を元にしたSQLの書き方を特訓していきます。

必ず実際にMySQLを動かしてphpMyAdminから問題のテーブルを作成してSQLを実行（「SQL」というタブから行えます）しましょう。（じゃないと練習にならないので）

鬼練１１：テーブル同士の結合（JOIN）

今までは「１つのテーブル」に対してデータを取ってくるためのSQLでした。

でも、これだと「ユーザー情報（顧客情報）」を取ってきてなんやかんやする（例えば、プロフィール編集ページを表示した時にユーザー情報を取ってきて、すでに入力された状態にしておく。とか）ことは出来ますが、

楽天市場やメルカリのような「商品の詳細ページ」なんかのページで

「商品情報」と共に「その商品を登録した売り手のユーザー情報」も表示したい場合

にはどうしたらいいんでしょ？？

表示するためには

ユーザーが登録した他の情報（ユーザーに紐づいている他のテーブルの情報）を取得したい

わけですね。

ここらへんもWEBサービス部で教えてる内容ですね。

では、前回同様に下記の仕様書にあるテーブルを作って、そのテーブルにあるデータを「検索（SELECT）」してみましょう。

仕様書

下記要件に従ってSQLを記述してください。

下記の構造のテーブル「users」「products」をphpMyAdminから作成し、そのテーブルにレコードをそれぞれ2つ（計４レコード）入れてください

挿入するデータは下記の４つです。

○users１つ目のデータ

id：連番（1）

name：ウェブカツおじさん

email：test@test.com

gender：1

○users２つ目のデータ

id：連番（2）

name：筋トレおじさん

email：test@test.com

gender：1

○products１つ目のデータ

id：連番（1）

user_id：2

name：プロテイン

detail：ホエイプロテインです。筋肉の生成に必要なものです。

○products２つ目のデータ

id：連番（2)

user_id：2

name：HMB

detail：HMBは筋肉の生成を促進します。プロテインと併用すると効果的です。プロテインを摂取せずにHMBを摂るだけでも筋肉はついていきます。

 

としてください。

上記データから

usersテーブルのidが2のユーザーに紐づく、商品テーブルのレコードとカラムを「全て」

取得してください。

ヒント

「あるテーブルのレコードに紐づく他のテーブルを取得する」ということがポイントですね。

それには「JOIN句」というものがあります。

ちなみに

今までの普通のSELECT文でも取得することは出来なくはないです。

1. まず該当のユーザー情報をDBから取得してくる
2. そのユーザーのIDを元に今度は商品テーブルをSELECT文でまた検索する

って方法です。

でも、そうしちゃうと２回もDBへアクセスしなきゃいけないわけですね。

• あなた：このユーザー情報ください〜
• DB：はいよー
• あなた：あ、すいませんそしたらこのユーザーの商品情報ももらえますか？
• DB：はいよー

ってことです。

その度に「SQLを送って、DBがそのSQLを元にDB内を調べ、結果を送り返す」という一連の通信が発生するわけで、通信が発生するってことは「その分、時間がかかる」ってことですからね。

あとProductsテーブルの「FOREIGN」というのは「FOREIGN KEY（フォーリンキー）」のことです。

外部キー制約

ってものです。

外部キー制約をつけたカラムの名前には

「ユーザーID（FK）」

という感じで分かりやすいように「（FK）」とつけときます。

とは言っても、これ

「カラム名」につけるわけじゃなくて、こういう「テーブル設計図」を作った時の設計図にただつけとくだけ

なので、間違ってもカラム名を

「ユーザーID（FK）」

としてテーブル作らないでくださいね。（え、もうやっちゃった？じゃあ、カラム名を直しとこうね。直し方わかるよね？？）

ちなみにテーブル設計図のことを

ER図

って言います。

ER図は「書き方」「書式」が決まってて「テーブルの構成」と「テーブル同士の関係性（テーブル同士のリレーションっていいます）」がパッと分かるように書くものです。

なので、厳密にはこの鬼練で出てきたテーブル設計図（テーブル構成の表）はER図じゃありませんけどね。

答え

user_idをつけよう

まず、テーブル作る時に今回新しく出てきたProductsテーブルについてです。

「ユーザーが商品を登録する」場合、その「登録した商品の情報」をもちろんDBに保存しとかなきゃいけません。

なので、商品テーブルを作ってあげるわけです。（ここらへんもWEBサービス部でやってますね）

ただ、商品テーブルを普通に

• id
• name
• detail

だけのカラムにしちゃうと困ったことになります。

だって、商品テーブルの情報だけ見たら、後から見返した時に

この商品って誰のユーザーが登録した商品？

ってなりますからね。

なので、商品テーブルに「この商品はどのユーザーのものか」を識別できるようにuser_idをつけるわけです。

現実世界の紙の台帳でイメージしても同じですよね。

「顧客台帳」と「商品台帳」のそれぞれの台帳があって、商品台帳に「どの顧客の商品か」を識別するもの（名前だったり）が必要ですからね。

外部キー制約

次に「FOREIGN　KEY」についてです。

外部キー制約についてはこれなんかをみてください。

FOREIGN KEY制約(外部キー制約を設定する) | MySQLの使い方

外部キー制約をつけることで「テーブル同士の整合性」が保ちやすくなるわけですね。

もし、今回のように商品テーブルとユーザーテーブルを紐づけて外部キー制約をつければ、

すでに商品を登録しているユーザーの情報が削除出来ないように出来る

わけです。

だって、ユーザー情報削除されてて、商品の情報だけ残ってたらおかしなことになりますよね？

メルカリで出品されている商品を開いてみたら

ユーザーが既にいない。。

って状態になるわけですから。

「いや、買えないじゃんこれ。。。」

って事態になるわけです。

もちろん、それだと

「じゃあ、ユーザーが削除できないとユーザーが退会できなくなっちゃうじゃん！」

ってなるわけですが、

外部キー制約というのは

ユーザー情報を削除する前に紐づいている商品を削除しとけば削除できる

ようになっています。

（他にも、紐づけたユーザーテーブルのユーザーIDを勝手に変更すると怒られます。）

なので、ちゃんとユーザーが退会した時には

1. まずは、ユーザーがすでに登録した商品レコードを全部削除する
2. 次にユーザー情報を削除する

という順番で行えばきちんと削除が行えます。

とは言っても、ほとんどのWEBサービスでは、あとあとの問い合わせ対応のために退会してからもしばらくはユーザー情報を残しておかないと困っちゃうので、「物理削除」ではなく「論理削除」という方法でやりますけどね。

phpMyAdminでの外部キー制約の付け方

外部キー制約をphpMyAdminでカラムに付与したい場合、ちょっと操作が面倒です。

まずは、外部キー制約はつけずに普通にproductsテーブルを作成します。

作成したら、「テーブルの構造」タブから、user_idカラムの右端にある「その他」から「インデックス」を付与してあげます。

mysqlでは外部キー制約をつける場合には必ずインデックスもつける必要があるので。まずそれをつけてあげるわけです。

すると「インデックス」という下欄にuser_idが追加されてますね。これでuser_idカラムにインデックスがついているってことがわかります。

次に「Relation view」というリンクをクリックして画面を開きます。

すると外部キーを登録する画面が出てくるので、そこに下図のように各項目を入力します。

• Constraint name：（作った外部キー制約に名前をつけたい場合に入れる。空でもいい）
• ON DELETE：RESTRICT（外部キーのカラムと紐づけた親テーブルのレコードを削除しようとした時の挙動を決める。RESTRICTは「エラーになる」という挙動）
• ON UPDATE：RESTRICT（外部キーのカラムと紐づけた親テーブルのカラムを更新しようとした時の挙動を決める。RESTRICTは「エラーになる」という挙動）
• カラム：user_id（外部キーをつけたい対象のカラムを指定する）
• データベース：oniren01（外部キーをつけるカラムと紐づけるデータベース名を指定。ここではoniren01というDBを作ってるため、それを指定）
• テーブル：users（外部キーをつけるカラムと紐づけるテーブル）
• カラム：id（外部キーをつけるカラムと紐づけるテーブルのカラム）

MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？

これで、あとは保存すれば、外部キーが下図のように１個出来上がってるはずです。

これで外部キー制約がつけられたってことです。

内部結合（INNER JOIN）

では、このテーブルのレコードを更新するためのSQLは？というと

になります。

SQLの「外部結合」と呼ばれるものですね。WEBサービス部でやりましたね、これも。

テーブルとテーブルをくっつけて１個のSQLだけで両方のテーブルの情報を取得できる書き方ですね。

という形で、

「INNER JOIN」と書いたあとに大元のテーブル（今回だとusers）にくっつけたいテーブルを指定し、「ON句」のあとにそのテーブル同士が対応するカラムを指定する

ということです。

「INNER JOIN」の他にも「外部結合」という「RIGHT JOIN」「LEFT JOIN」というものもありました。

違いを答えられない人はもう１度見ましょうね。大事なんで。

ちなみに今回の場合、INNER JOINを使いましたが、RIGHT JOINでもLEFT JOINでも「今回の場合に限り」間違いではないです。

SQLの書き方は一緒ですよね。

こうなるだけです。

ただし、

取得できるデータが違う

んでしたね。

単にテーブルを結合して取得するにしても、

• LEFT JOIN
• 左側のテーブル（今回だとusersの方）にだけ存在するレコードも取得する
• RIGHT JOIN
• 右側のテーブル（今回だとproductsの方）にだけレコードが存在する場合も取得する
• INNER JOIN
• 両方のテーブルにレコードが存在する場合のみそのレコードを取得する

という違いがあるんでしたね。

今回の例で言えば

• LEFT JOIN
• usersテーブルにidが2のユーザーがいて、商品レコードはまだない（そのユーザーが商品未登録）でもそのユーザーレコードも取得される
• RIGHT JOIN
• productsテーブルにuser_idが2のユーザーが登録した商品レコードがあるが、usersにはidが2のユーザーは存在しなくても、その商品レコードも取得される（今回の場合だと外部キー制約があるのでこのケースは起こり得ない）
• INNER JOIN
• usersテーブルにidが2のユーザーがいて、productsテーブルにuser_idが2の商品があった場合にのみ、そのレコードを取得する

ということです。

なので、LEFT JOINとRIGHT JOINは

INNER JOIN＋αでどっちかのテーブルにだけデータがある場合も取得する

ってものなわけですね。

（え、違いわかってなかったって？そりゃ、動画をただ流して見ているだけで練習してないとそうなりますよ？何度も言ってますが自分で試さなきゃ力はつきません。理解できません。）

なので、今回入っているデータの場合だと

どっちでも取得できるレコードは同じ

なので、結果同じだからどっちでもOKです。

もし、データが違ってきてしまったら取得できる結果が違ってきてしまうので、使い分けは出来なきゃダメですよ？