ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
内部結合(INNER JOIN)の使い方を紹介します。
ORACLE DATABASE 12c SQL基礎(1Z0-061)試験において、「結合」、「副問合せ」は重要であり、かつSQL初学者には、とっつきにくい(イメージしずらい)項目だと思います。
今回は「結合」の一つである内部結合(INNER JOIN)を使いこなせるように、わかりやすく解説します。
この記事で使っているテーブルのデータは、本記事の最後にデータ作成用のSQLを記載しているので、実際に自分のパソコンでもSQLを試してみてください。
目次
- 結合について
- 内部結合(INNER JOIN)の書き方
- INNER JOINのサンプル1
- INNER JOINのサンプル2
- INNER JOINのサンプル3 (自己結合)
- 本記事で使用しているテーブル・データ
結合について
複数の表を「主キー」と「外部キー」でつなぎ合わせることを「結合」と言います。
結合には以下表のように主に「内部結合」「外部結合」「クロス結合」があります。
| 結合タイプ | 説明 |
|---|---|
| 内部結合 | 結合条件に一致する行だけを返す |
| 外部結合 | 結合条件に一致する行と一致しない行も返す |
| クロス結合 | 行の総組み合わせを返す（デカルト積） |
内部結合は、結合条件に一致した行だけを返す結合です。
以下の画像では、従業員テーブル(employee)と部門テーブル(department)をdepartment_idで結合しています。
employeeテーブルのdepartment_idは外部キー、departmentテーブルのdepartment_idは主キーです。
ExcelのVLOOK UP関数でemployeeテーブルのdepartment_idを基にdepartmentテーブルの情報を取得するイメージで考えるとわかりやすいかと思います。
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テーブルを結合します。
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;
INNER JOINのサンプル2
employeeテーブルで従業員の一覧を取得する際に、genderテーブルで従業員の性別もあわせて一覧で取得する例を考えてみましょう。
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;
INNER JOINのサンプル3 (自己結合)
employeeテーブルの従業員一覧を取得する際に、manager_idに紐づく名前も取得する例を見てみましょう。
今回は複数の表を結合するのではなく、1つの表に存在する外部キーと主キーで結合する「自己結合」という結合になります。
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;
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;
以上が内部結合の基本的な使い方になります。
本記事で使用しているテーブル・データ
本記事で使用しているテーブルとデータは以下の通りです。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 |
