内部結合
内部結合はそれぞれのテーブルの指定した列の値が一致するデータだけを取得します。基本となる構文は次の通りです。
SELECT table_name.col_name, ... FROM tbl_name1 INNER JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2;
結合の対象となるテーブルをFROMの後とINNER JOINの後に指定します。そしてそれぞのテーブルの結合するカラムをONの後にイコール(=)で結んで指定します。
内部結合ではそれぞれのテーブルの指定したカラムの値が同じものを1つのデータとして取得します。どちらかのテーブルにしか値が存在しないデータは取得されません。
※ MySQLではINNER JOINの代わりにCROSS JOINにONで結合規則を指定しても同じです。
テーブルの結合
具体的な例で見てみます。次のような2つのテーブルを用意しました。
goods :
| code | cateid | name |
|---|---|---|
| B001 | 1 | 旅行に行こう |
| D001 | 2 | 地球の歴史 |
| F001 | 3 | 栗きんとん |
| D002 | 2 | 宇宙旅行 |
| F002 | 3 | ロールケーキ |
| D003 | 2 | 七人の勇者 |
| B002 | 1 | 三国志 |
| S001 | 5 | 表計算ソフト |
| B003 | 1 | 趣味と実益 |
cate :
| id | name |
|---|---|
| 1 | 和書 |
| 2 | DVD |
| 3 | 食品 |
| 4 | 家電 |
内部結合を行うには、2つのテーブルで結合の対象となるカラムを指定します。例えば次のように指定します。
SELECT * FROM goods INNER JOIN cate ON goods.cateid = cate.id;
上記の場合、「goods」テーブルの「cateid」カラムと「cate」テーブルの「id」カラムを使って結合します。
データの取得
データの取得は次のように行われます。
まず「goods」テーブルの最初のデータの「cateid」カラムの値に対して、「cate」テーブルの「id」カラムの中に同じ値があるかどうかを調べます。存在した場合は「goods」テーブルのデータと「cate」テーブルの一致したデータと結合させてまとめて一つのデータとして取得します。
| code | cateid | name | id | name |
|---|---|---|---|---|
| B001 | 1 | 旅行に行こう | 1 | 和書 |
同じように「goods」テーブルのデータを順に結合を行っていきます。もし「goods」テーブルの「cateid」カラムの値が「cate」テーブルの「id」カラムの値の中に一致するものがなかった場合はデータを取得しません。今回の場合であれば「cateid」カラムの値が5のデータは「cate」テーブルの「id」カラムの中に一致するものありませんので取得されません。
| code | cateid | name | id | name |
|---|---|---|---|---|
| B001 | 1 | 旅行に行こう | 1 | 和書 |
| D001 | 2 | 地球の歴史 | 2 | DVD |
| F001 | 3 | 栗きんとん | 3 | 食品 |
| D002 | 2 | 宇宙旅行 | 2 | DVD |
| F002 | 3 | ロールケーキ | 3 | 食品 |
| D003 | 2 | 七人の勇者 | 2 | DVD |
| B002 | 1 | 三国志 | 1 | 和書 |
| B003 | 1 | 趣味と実益 | 1 | 和書 |
結合されたデータの中から必要なカラムを指定してデータを取得します。
取得するカラムの指定方法
結合された全データの中で、どのカラムを取得するかはSELECT文の後ろで指定します。カラムは「テーブル名.カラム名」の形式で指定して下さい。例えば「goods」テーブルの「code」カラムと「cate」テーブルの「name」カラムの値を取得するには次のように記述します。
SELECT goods.code, goods.name, cate.name FROM goods INNER JOIN cate ON goods.cateid = cate.id;
どちらかのテーブルにしか存在しないカラムを指定する場合はテーブル名を省略できます。例えば「goods」テーブルの「code」カラムを取得したい場合、「cate」テーブルには「code」カラムは存在しませんので単に「code」と記載できます。それに対して「goods」テーブルの「name」カラムを取得したい場合には「cate」テーブルにも「name」カラムが存在するので必ずテーブル名を付けて「goods.name」と指定する必要があります。
SELECT code, goods.name, cate.name FROM goods INNER JOIN cate ON goods.cateid = cate.id;
上記のSQL文で実際に取得できるデータは次の通りです。
| code | name | name |
|---|---|---|
| B001 | 旅行に行こう | 和書 |
| D001 | 地球の歴史 | DVD |
| F001 | 栗きんとん | 食品 |
| D002 | 宇宙旅行 | DVD |
| F002 | ロールケーキ | 食品 |
| D003 | 七人の勇者 | DVD |
| B002 | 三国志 | 和書 |
| B003 | 趣味と実益 | 和書 |
複数のデータでカラムの値が一致する場合
では同じテーブルを対象にした内部結合であってもテーブルの指定を入れ替えてデータを取得してみます。
SELECT * FROM cate INNER JOIN goods ON cate.id = goods.cateid;
まず「cate」テーブルの最初のデータの「id」カラムの値に対して、「goods」テーブルの「cateid」カラムの中に同じ値があるかどうかを調べます。存在した場合は「cate」テーブルのデータと「goods」テーブルの一致したデータと結合させてまとめて一つのデータとして取得します。
今までと異なるのは「cate」テーブルの最初のデータの「id」カラムの値に一致するデータが「goods」テーブルには3つあるということです。この場合、それぞれのデータに対して結合が行われます。
| id | name | code | cateid | name |
|---|---|---|---|---|
| 1 | 和書 | B001 | 1 | 旅行に行こう |
| 1 | 和書 | B002 | 1 | 三国志 |
| 1 | 和書 | B003 | 1 | 趣味と実益 |
同じように「cate」テーブルのデータを順に結合を行っていきます。もし「cate」テーブルの「id」カラムの値が「goods」テーブルの「cateid」カラムの値の中に一致するものがなかった場合はデータを取得しません。今回の場合であれば「id」カラムの値が4のデータは「goods」テーブルの「cateid」カラムの中に一致するものありませんので取得されません。
| id | name | code | cateid | name |
|---|---|---|---|---|
| 1 | 和書 | B001 | 1 | 旅行に行こう |
| 1 | 和書 | B002 | 1 | 三国志 |
| 1 | 和書 | B003 | 1 | 趣味と実益 |
| 2 | DVD | D001 | 2 | 地球の歴史 |
| 2 | DVD | D002 | 2 | 宇宙旅行 |
| 2 | DVD | D003 | 2 | 七人の勇者 |
| 3 | 食品 | F001 | 3 | 栗きんとん |
| 3 | 食品 | F002 | 3 | ロールケーキ |
サンプル
実際に試してみます。まず次のようなテーブルを作成します。
mysql> create table goods (code varchar(5), cateid int, name varchar(10));
mysql> create table cate (id int, name varchar(10));
テーブルには次のようなデータを追加してあります。
「goods」テーブルの「cateid」カラムと「cate」テーブルの「id」カラムを内部結合してデータを取得します。
mysql> select * from goods inner join cate on goods.cateid = cate.id;
必要なカラムだけを指定してデータを取得します。
mysql> select goods.code, goods.name, cate.name from goods
-> inner join cate on goods.cateid = cate.id;
取得したデータの2つの列が同じ名前になってしまっていますので別名を設定します。
mysql> select goods.code, goods.name, cate.name as category from goods
-> inner join cate on goods.cateid = cate.id;
( Written by Tatsuo Ikura )
DBOnline