オーロラさんの勉強帳

SESの客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

【SQL】内部結合(INNER JOIN)の基本を学ぶ (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
内部結合(INNER JOIN)の使い方を紹介します。

ORACLE DATABASE 12c SQL基礎(1Z0-061)試験において、「結合」、「副問合せ」は重要であり、かつSQL初学者には、とっつきにくい(イメージしずらい)項目だと思います。
今回は「結合」の一つである内部結合(INNER JOIN)を使いこなせるように、わかりやすく解説します。

この記事で使っているテーブルのデータは、本記事の最後に記載しているので実際に自分のパソコンでもSQLを試してみてください。


目次

結合について

複数の表を「主キー」と「外部キー」でつなぎ合わせることを「結合」と言います。
結合には以下表のように主に「内部結合」「外部結合」「クロス結合」があります。

結合タイプ 説明
内部結合 結合条件に一致する行だけを返す
外部結合 結合条件に一致する行と一致しない行も返す
クロス結合 行の総組み合わせを返す(デカルト積)


内部結合は、結合条件に一致した行だけを返す結合です。
以下の画像では、従業員テーブル(employee)と部門テーブル(department)をdepartment_idで結合しています。
employeeテーブルのdepartment_idは外部キー、departmentテーブルのdepartment_idは主キーです。
ExcelのVLOOK UP関数でemployeeテーブルのdepartment_idを基にdepartmentテーブルの情報を取得するイメージで考えるとわかりやすいかと思います。

f:id:auroralights:20210808201412p:plain

SELECT
 a.employee_id,
 a.name,
 a.department_id,
 b.department_name,
 b.tel
FROM employee a
INNER JOIN department b
ON a.department_id = b.department_id
order by employee_id;

内部結合(INNER JOIN)の書き方

ON句を使った内部結合の書き方は以下の通りです。

SELECT
[表1.]列名, [表2.]列名 ,・・・・
FROM 表1 [INNER] JOIN 表2
ON 表1.外部キー = 表2.主キー
[WHERE 検索条件]
・・・

※INNERは省略可能です。


内部結合のPOINT

  • ON句に結合条件を記載する
  • 等価結合の場合は、結合条件に「=(イコール)」を使用する
  • 結合する複数の表に共通して存在する列名(あいまいな列)は、表接頭辞で修飾する (表名.列名)
    ※表を区別するために表接頭辞での修飾が必要。
  • 表接頭辞として、表名よりも短い表別名の使用を推奨。可読性の向上と、SQL文が短くなるためにデータベースが使用するメモリの使用量が削減できる。
  • 表接頭辞はあいまいな列以外はつけなくてもエラーにはなりません。ただし、どの列があいまいかどうかを考えながらSQLを作成するよりも、すべての列に表接頭辞をつける方が楽なこともあります。また、表接頭辞をすべての列につけることで文の解析速度が向上してパフォーマンスを向上させることもあるので、全列に表接頭辞を書くようにしましょう。(内部結合に限らず)

SQLの処理順番

内部結合に限りる話ではありませんが、SQLを書いたり、読んだりする際にはSQLの評価順(実行順)を意識しましょう。

まず、FROM句、JOIN ON句で基となるテーブルを取り出し、WHERE句の条件でそのテーブルのレコードを絞ります。次にGROUP BY句でグループ化した後に、グループ化されたデータに対して条件を指定してレコードを絞ります。
そして、DISTINCT、SELECTで必要な列を取得し、並べ替えを行います。

<SQLの評価順>

FROM ⇒
JOIN ON ⇒
WHERE ⇒
GROUP BY ⇒
HAVING ⇒
DISTINCT ⇒
SELECT ⇒
ORDER BY

INNER JOINのサンプル1

employeeテーブルで従業員の一覧を取得する際に、従業員が所属している部門名、電話番号も取得したい場合のSQLを考えてみましょう。
employeeテーブルからはemployee_id、name、department_id、departmentテーブルからはdepartment_name、telを取得します。
department_idでemployeeテーブルとdepartmentテーブルを結合します。

f:id:auroralights:20210808213341p:plain

SELECT
 employee_id,
 name,
 employee.department_id,
 department_name,
 tel
FROM employee
INNER JOIN department
ON employee.department_id = department.department_id
order by employee_id;


department_id列はemployeeテーブル、departmentテーブルの両方に共通して存在するあいまいな列なので、表名で修飾しています。
もし、department_idを表名で修飾していないと、「ORA-00918:列の定義が未確定です。」というエラーが発生します。


以下のように表名で全列を修飾すると、どの列がどの表かわかりやすいですが、SQLが長くなり可読性を損なうことがあります。

SELECT
 employee.employee_id,
 employee.name,
 employee.department_id,
 department.department_name,
 department.tel
FROM employee
INNER JOIN department
ON employee.department_id = department.department_id
order by employee.employee_id;


以下のようにemployeeテーブルに「a」、departmentテーブルに「b」と表別名を指定して、表接頭辞として各列を修飾するとSQLが短く、可読性も高くなります。SQLが短くなり、データベースが使用するメモリの使用量も削減できます。

SELECT
 a.employee_id,
 a.name,
 a.department_id,
 b.department_name,
 b.tel
FROM employee a
INNER JOIN department b
ON a.department_id = b.department_id
order by a.employee_id;


f:id:auroralights:20210808215255p:plain

INNER JOINのサンプル2

employeeテーブルで従業員の一覧を取得する際に、genderテーブルで従業員の性別もあわせて一覧で取得する例を考えてみましょう。

f:id:auroralights:20210809214103p:plain

employeeテーブルとgenderテーブルをgender_idで結合(ON a.gender_id = b.gender_id)し、データを抽出します。
今回はINNERを省略しています。

SELECT
 a.*,
 b.gender
FROM employee a
JOIN gender b
ON a.gender_id = b.gender_id;

f:id:auroralights:20210809214656p:plain


INNER JOINのサンプル3 (自己結合)

employeeテーブルの従業員一覧を取得する際に、manager_idに紐づく名前も取得する例を見てみましょう。
今回は複数の表を結合するのではなく、1つの表に存在する外部キーと主キーで結合する「自己結合」という結合になります。

f:id:auroralights:20210809215202p:plain

SELECT
 a.*,
 b.name AS manager_name
FROM employee a
JOIN employee b
ON a.manager_id = b.employee_id
ORDER BY a.employee_id;

f:id:auroralights:20210809215804p:plain

a.manager_id = b.employee_idに一致する行しか取得しないため、manager_idが入っていないデータは抽出されません。
もし、manager_idが入っていないデータも抽出したい場合は、「JOIN」の部分を「LEFT OUTER JOIN」と変更して外部結合にすることで取得することが可能です。

外部結合

SELECT
 a.*,
 b.name AS manager_name
FROM employee a
LEFT OUTER JOIN employee b
ON a.manager_id = b.employee_id
ORDER BY a.employee_id;

f:id:auroralights:20210809215932p:plain


以上が内部結合の基本的な使い方になります。

本記事で使用しているテーブル・データ

本記事で使用しているテーブルとデータは以下の通りです。CREATE TABLEで作成後、A5SQLなどでデータを貼り付けて試してみていただけたらと思います。

departmentテーブル

create table department (
department_id NUMBER(2) constraint department_pk PRIMARY KEY,
department_name VARCHAR(15),
tel CHAR(12)
)

departmentテーブルのデータ

department_id department_name tel
10 総務部 0000-00-0000
20 経営企画部 0000-00-0001
30 営業部 0000-00-0002
40 販売促進部 0000-00-0003
50 監査部 0000-00-0004



genderテーブル

create table gender (
gender_id NUMBER(2) constraint gender_pk PRIMARY KEY,
gender VARCHAR(5)
)


genderテーブルのデータ

gender_id gender
10 man
20 woman


employeeテーブル

create table employee (
employee_id NUMBER(4) constraint employee_pk PRIMARY KEY,
name VARCHAR(10),
gender_id NUMBER(2) REFERENCES gender(gender_id),
department_id NUMBER(2) REFERENCES department(department_id),
hire_date DATE,
manager_id NUMBER(4) REFERENCES employee(employee_id)
)


employeeテーブルのデータ

employee_id name gender_id department_id hire_date manager_id
1001 james 10 10 2020/1/1
1002 robert 10 10 2020/1/1 1001
1003 mary 20 10 2020/1/1 1001
1004 patricia 20 20 2020/1/1
1005 jennifer 20 20 2020/2/1 1004
1006 john 10 30 2020/2/1
1007 william 10 30 2020/2/1 1007
1008 linda 20 30 2020/2/1 1007
1009 elizabeth 20 40 2020/3/1
1010 david 10 40 2020/3/1 1009
1011 richard 10 50 2020/3/1
1012 susan 20 50 2020/4/1 1011
1013 daniel 10 10 2020/4/1 1001
1014 matthew 10 20 2020/4/1 1004
1015 donald 10 30 2020/4/1 1007
1016 joshua 10 40 2020/4/1 1009
1017 paul 10 10 2021/4/1 1001
1018 carol 20 10 2021/4/1 1001
1019 amanda 20 20 2021/4/1 1004
1020 deborah 20 30 2021/5/1 1007