内部結合(INNER JOIN句)

広告

2つのテーブルを結合してデータを取得する方法の中で、指定したそれぞれのテーブルのカラムの値が一致するデータだけを取得する方法が内部結合です。ここでは内部結合を行うためのINNER JOIN句の使い方について解説します。

1.INNER JOIN句の使い方
2.データの結合方法
3.結合が複数のデータで一致する場合
4.取得するデータのカラム指定方法

INNER JOIN句の使い方

INNER JOIN句を使うための書式は次の通りです。 

SELECT テーブル名.カラム名, ... FROM テーブル名1
 INNER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;

長いので簡略化すると次のようになります。 

SELECT (取得するカラム) FROM テーブル名1 INNER JOIN テーブル名2 ON (結合条件);

結合条件のところで同じ値があるかどうかを調べるそれぞれのテーブルのカラムを指定します。取得するデータは2つのテーブルのカラムが混合したものとなりますので、取得するカラムの指定には「カラム名」ではなくどのテーブルのカラムなのかを表すために「テーブル名.カラム名」の形で指定します。

それでは簡単な例を使ってどのように使用するのかについて確認します。社員の一覧が登録された「staff」テーブルと、部署が登録された「dept」テーブルを用意しました。 

create table staff(id integer, name text, deptid integer);

create table dept(id integer, name text);

p1-1

staffテーブルには次のようなデータが入っています。

p1-2

deptテーブルには次のようなデータが入っています。

p1-3

次にどちらのテーブルにどちらのテーブルを結合するのかを決めます。2つのテーブルを結合を行う場合、どちらのテーブルを先に記述するのかによって結果が少し異なります。今回はstaffテーブルとdeptテーブルを結合しますので次の2通りの記述ができますがどちらでも同じではありません。(どう違ってくるのは後で説明します)。 

select * from staff inner join dept on (結合条件);

select * from dept inner join staff on (結合条件);

次に結合条件です。内部結合を行うには2つのテーブルで結合の対象となるカラムを指定します。今回はstaffテーブルの「deptid」とdeptテーブルの「id」が対象のカラムとなります。 

select * from staff inner join dept on staff.deptid = dept.id;

では実際に2つのテーブルを内部結合し、データを取得してみます。 

select * from staff inner join dept on staff.deptid = dept.id;

p1-4

このように内部結合した場合には取得できるデータは2つのテーブルのデータが結合されたものになります。どのようなルールに従って取得するデータが結合されているのかについてはこの後説明します。

データの結合方法

先ほどのサンプルを元にどのようにテーブルが内部結合されるのかについて確認していきます。まずSELECT文の最初に記述されたテーブルの1つ目のデータに対して結合が行われます。 

id name      deptid
--------------------
1  Yamada    1

結合条件は「staff.deptid = dept.id」です。このデータの「deptid」の値は「1」ですので、deptテーブルのデータの中で「id」カラムの値が「1」のものがあるかどうかを調べます。今回は1つ見つかりました。 

id name
------------
1  Sales

見つかった場合にはこの2つのテーブルのデータを結合して1つのデータとして返します。 

id name      deptid   id name
-------------------------------
1  Yamada    1        1  Sales

staffテーブルのデータを順番に同じように結合していきます。 

id name      deptid   id name
-----------------------------------
1  Yamada    1        1  Sales
2  Suzuki    3        3  Publicity
3  Kuroda    2        2  Technical
4  Ishida    1        1  Sales

もし結合条件に一致しないデータがあった場合、内部結合の場合にはそのデータは取得されません。下記のデータの場合、「deptid」カラムの値が「5」ですが、deptテーブルのデータの中には「id」カラムの値が「5」のものがありませんのでこのデータは取得されません。 

id name      deptid
--------------------
5  Takahashi 5

staffテーブルのデータを最後まで同じ手順で結合したら終了です。最終的に取得できるデータは次の通りです。 

id name      deptid   id name
-----------------------------------
1  Yamada    1        1  Sales
2  Suzuki    3        3  Publicity
3  Kuroda    2        2  Technical
4  Ishida    1        1  Sales
6  Eguchi    2        2  Technical
7  Adachi    1        1  Sales

これは先ほど実際に試した結果と一致します。内部結合の場合には、結合条件に一致しないデータは取得されない点に注意して下さい。

結合が複数のデータで一致する場合

先ほどのサンプルの場合、staffテーブルの「deptid」カラムの値と同じ値を持つdeptテーブルのデータが1つしかありませんでした。ただ場合によっては複数のデータと一致する場合があります。その場合は一致する全てのデータに対してそれぞれ結合が行われデータを取得します。

それでは実際にどのように結合されるのかを確認してみます。先ほどまでのサンプルとは逆に、deptテーブルに対してstaffテーブルを結合すると次のようなデータを取得することができます。 

select * from dept inner join staff on dept.id = staff.deptid;

p1-5

ではどのようにテーブルが内部結合されるのかについて確認していきます。まずSELECT文の最初に記述されたテーブルの1つ目のデータに対して結合が行われます。 

id name
------------
1  Sales

結合条件は「dept.id = staff.deptid」です。このデータの「id」の値は「1」ですので、staffテーブルのデータの中で「deptid」カラムの値が「1」のものがあるかどうかを調べます。今回は3つ見つかりました。 

id name      deptid 
--------------------
1  Yamada    1
4  Ishida    1
7  Adachi    1

見つかった3つのデータに対してそれぞれ2つのテーブルデータを結合して1つのデータとして返します。 

id name     id name      deptid 
--------------------------------
1 Sales     1  Yamada    1
1 Sales     4  Ishida    1
1 Sales     7  Adachi    1

deptテーブルのデータを順番に同じように結合していきます。 

id name       id name      deptid 
----------------------------------
1 Sales       1  Yamada    1
1 Sales       4  Ishida    1
1 Sales       7  Adachi    1
2 Technical   3  Kuroda    2
2 Technical   6  Eguchi    2
3 Publicity   2  Suzuki    3

もし結合条件に一致しないデータがあった場合、内部結合の場合にはそのデータは取得されません。下記のデータの場合、「id」カラムの値が「4」ですが、staffテーブルのデータの中には「deptid」カラムの値が「4」のものがありませんのでこのデータは取得されません。 

id name
--------------------
4  Operation

deptテーブルのデータを最後まで同じ手順で結合したら終了です。最終的に取得できるデータは次の通りです。 

id name       id name      deptid 
----------------------------------
1 Sales       1  Yamada    1
1 Sales       4  Ishida    1
1 Sales       7  Adachi    1
2 Technical   3  Kuroda    2
2 Technical   6  Eguchi    2
3 Publicity   2  Suzuki    3

このように結合する時に複数のデータに一致する場合にはそれぞれのデータに対して結合が行われます。

取得するデータのカラム指定方法

SELECT文では「SELECT カラム名1, カラム名2, ・・・」のような記述をしますが、テーブルを結合した場合は1つのデータに複数のデータのカラムが含まれることになります。複数のテーブルで同じカラム名が使用されていることもあるので、テーブルを結合した場合には「カラム名」ではなく「テーブル名.カラム名」のように指定します。

先ほどのサンプルでカラム名を指定せずにデータを取得した場合は次のようになります。 

select * from staff inner join dept on staff.deptid = dept.id;

p1-6

取得したデータのカラムには「ID」カラムが2つ、「NAME」カラムも2つ含まれています。このように複数のテーブルに同じ名前のカラムがある場合にこのカラムをを指定して取得したい場合には次のように「テーブル名.カラム名」の形で指定します。 

select staff.id, staff.name, dept.name from staff 
  inner join dept on staff.deptid = dept.id;

p1-7

どちらかのテーブルにしか含まれておらずテーブル名を省略してもどちらのテーブルのカラムか分かる場合には「テーブル名.」の部分を省略して「カラム名」だけでも可能です。(下記では「deptid」カラムがそれに当たります)。 

select deptid, dept.name from staff 
  inner join dept on staff.deptid = dept.id;

p1-8

2つのテーブルに同じカラム名があるのにテーブル名を指定せずにカラム名だけを記述した場合は次のような「Error: ambiguous column name: カラム名」というエラーとなります。 

select name from staff inner join dept on staff.deptid = dept.id;

p1-9

テーブルの内部結合の方法について解説しました。

( Written by Tatsuo Ikura )