テーブルを内部結合してデータを取得する方法～inner join～

テーブルを内部結合してデータを取得する方法について紹介していきます。

前提条件

ユーザー管理をしているUSERSテーブルと都道府県一覧を管理しているprefectureテーブルを結合してデータを取得します。

使用するテーブル：usersテーブルとprefectureテーブル

usersテーブル

+----+----------+------+------------------+--------+---------------+
| id | last_name| age  | email            | gender | prefecture_id |
+----+----------+------+------------------+--------+---------------+
|  1 | 柴犬     |    7 | shibainu@dog.com |      1 |            13 |
|  2 | コーギー |    8 | corgi@dog.com    |      1 |            42 |
|  3 | ダックス |   10 | daks@dog.com     |      2 |            11 |
|  4 | パグ     |    3 | pug@dog.com      |      2 |            10 |
|  5 | シーズー |   17 | shi-tzu@dog.com  |      2 |            11 |
|  6 | 秋田犬   |   12 | akitainu@dog.com |      1 |             5 |
+----+----------+------+------------------+--------+---------------+
6 rows in set (0.000 sec)

prefectureテーブル

+----+----------+--------------+
| id | name     | name_kana    |
+----+----------+--------------+
|  1 | 北海道   | ホッカイドウ |
|  2 | 青森県   | アオモリケン |
|  3 | 岩手県   | イワテケン   |
| ・ |   ・     |      ・      |
| ・ |   ・     |      ・      |
| ・ |   ・     |      ・      |
| 47 | 沖縄県   | オキナワケン |
+----+----------+--------------+
47 rows in set (0.001 sec)

条件

• usersテーブルのprefecture_idとprefectureテーブルのidを紐づける
• usersテーブルの情報を取得する際にprefecture_idではなく県名(prefectureテーブルのnameカラム)を表示させる
• 取得するカラム
  usersテーブル：id,last_name
  prefectureテーブル：name

prefecture_idの値と都道府県名の紐づけ

• 13　　 ⇒　　東京都
• 42　　⇒　　長崎県
• 11　　⇒　　埼玉県
• 10　　⇒　　群馬県
• 5　　⇒　　秋田県

内部結合の使い方

構文

select 取得するカラム名 from 
メインのテーブル名 
inner join 結合させるテーブル名 on 
メインテーブル名.外部キーとなるカラム名 = 結合テーブル名.の主キーとなるカラム名;

コードの見方

使用するSQL文

select users.id,last_name,name from 
users 
inner join prefecture on 
users.prefecture_id = prefecture.id;

users.id,last_name,name
取得するカラム名は結合する二つのテーブルから指定することができます。両方のテーブルに同じカラム名がある場合はカラム名の前にテーブル名を付ける必要があります。

users.prefecture_id = prefecture.id;
結合する条件を指定しています。 usersテーブルのprefecture_idとprefectureテーブルのidを紐づけています。

出力結果

+----+-----------+--------+
| id | last_name | name   |
+----+-----------+--------+
|  1 | 柴犬      | 東京都 |
|  2 | コーギー  | 長崎県 |
|  3 | ダックス  | 埼玉県 |
|  4 | パグ      | 群馬県 |
|  5 | シーズー  | 埼玉県 |
|  6 | 秋田犬    | 秋田県 |
+----+-----------+--------+
6 rows in set (0.000 sec)

usersテーブルのprefecture_idに紐づいているprefectureテーブルの都道府県名が表示されています。

内部結合でテーブルを結合して条件を指定する

joinを利用してテーブルを結合してデータを取得する場合でもwhere句を利用した条件付けをすることができる

都道府県名が埼玉県のユーザーのみ取得する

select users.id,last_name,name from 
users 
inner join prefecture on 
users.prefecture_id = prefecture.id 
where name = '埼玉県';

出力結果

+----+-----------+--------+
| id | last_name | name   |
+----+-----------+--------+
|  3 | ダックス  | 埼玉県 |
|  5 | シーズー  | 埼玉県 |
+----+-----------+--------+
2 rows in set (0.001 sec)

補足：テーブル名に別名を付けて省略する

テーブル結合を利用して記述していくとカラム名の前に何度もテーブル名を書かなければいけないのでとても大変です。

そんな時に利用できるのがテーブル名に別名を付けて省略することです。

先ほど使用したコード

select users.id,last_name,name from 
users 
inner join prefecture on 
users.prefecture_id = prefecture.id;

別名を付けて省略したコード

select u.id,last_name,name from 
users u
inner join prefecture p on 
u.prefecture_id = p.id;

それぞれのテーブル名を以下のように省略して記載しました。

• usersテーブル ⇒ u
• prefectureテーブル ⇒ p

テーブル名やカラム名の別名のつけ方の詳細は以下となります