【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を記載しているので、実際に自分のパソコンでもSQLを試してみてください。

目次

結合について

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

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

内部結合は、結合条件に一致した行だけを返す結合です。
以下の画像では、従業員テーブル(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

【SQL】列別名(列見出し)の指定方法 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
列別名(列見出し)の指定方法を紹介します。

目次

デフォルトの列名

OracleのSELECT文で抽出した列名は、デフォルトでは以下のように表示されます。

• 列名を大文字で表示する
• 指定した式をそのまま表示する

SQLで列名の表示を確認してみます。

SELECT employee_id,name,salary * 12,salary + 10000 FROM test5;

列別名の指定方法

列別名は以下の2つの方法で指定することができます。

• 列名と列別名をスペースで区切る方法
• 列名と列別名をASキーワードで区切る方法

＜半角スペースで区切る方法＞
SELECT 列名 列別名 FROM テーブル名;

＜ASキーワードで列別名を指定する方法＞
SELECT 列名 AS 列別名 FROM テーブル名;

実際に半角スペース、ASキーワードで区切って列別名を指定してみよう。

SELECT
  employee_id 社員番号
  , name Name
  , salary * 12 AS 年間給与
  , salary + 10000 AS Bonus
FROM
  test5;

上記の場合だと「Name」、「Bonus」を指定したにもかかわらず、「NAME」、「BONUS」とすべて大文字で表示されてしまいました。
これはデフォルトの列の表示規則によるものです。

列別名で「大文字・小文字」を区別させたり、「スペース」を含む列別名を指定したりするには「二重引用符」で列別名を囲む必要があります。

列別名の命名規則

列別名はオブジェクトの命名規則に従う必要があります。

Oracle オブジェクトの命名規則

• 長さは30バイト以下
• 英数字(AからZ、aからz、0から9)を使用可能
  (日本語環境では、漢字、ひらがな、カタカナも使用可能)
• 特殊記号(#,$,_)が使用可能
• 名前は文字で開始する必要がある
  (数字、特殊記号は名前の先頭に使用不可 例：1EMP、#EMP 等)
• スペースは使用不可
• Oracleの予約語は使用不可
  予約語はORACLEですでに予約されている、つまり使用用途が定義されている単語です。TABLE、USER、SELECT 等
• 同じユーザが所有する別のオブジェクトと重複する名前は使用不可

二重引用符(ダブルクォーテーション)を使った列別名の指定方法

「大文字・小文字」を区別した列別名や、Oracleの命名規則に反した列別名を指定するためには、列別名を「二重引用符(")」で囲んで指定する必要があります。

列別名を二重引用符で囲む必要があるケース

• 列別名にスペースを含む
• 大文字・小文字を区別する
• 数字や記号で始まる

＜列別名 二重引用符＞
SELECT 列名 "列別名" FROM テーブル名;
SELECT 列名 AS "列別名" FROM テーブル名;

二重引用符を使って大文字・小文字を区別して列別名を指定します。
「Name」、「Bonus」とすべて大文字ではなく、大文字と小文字を区別した列別名で指定ができます。

SELECT
  employee_id 社員番号
  , name "Name"
  , salary * 12 AS 年間給与
  , salary + 10000 AS "Bonus"
FROM
  test5;

Oracleの命名規則に反するため、二重引用符で囲まないとエラーになる列別名を試してみましょう。

SELECT
  employee_id "社員 番号"
  , name "123"
  , salary * 12 AS "#a"
  , salary + 10000 AS "TABLE"
  , salary + 20000 AS "/+"
FROM
  test5;

各列別名を二重引用符で囲まないと、以下のような理由からエラーとなります。

• 「社員 番号」はスペースを含んでいるため
• 「123」は数字から始まる名前のため
• 「#a」は特殊記号から始まっているため
• 「TABLE」はORACLEの予約語のため
• 「/*」は記号のため

注意

列別名は一重引用符(シングルクォーテーション)では囲むことはできませんので注意しましょう。
大文字・小文字を区別したり、命名規則に反する列別名を囲む場合は、「二重引用符」で囲うことを覚えておきましょう。

以下のようなSQLは、一重引用符で列別名を囲んでいるのでエラーとなります。

SELECT
  employee_id '社員番号'
FROM
  test5;

【SQL】LISTAGG関数の使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
LISTAGG(リスタッグ)関数の使用方法を紹介します。

目次

LISTAGG(リスタッグ)関数とは

LISTAGG関数を使うと、指定した列の複数行の値を連結して1行にまとめることができます。
デリミタ(区切り記号)を指定することで、カンマ区切りなどでまとめることが可能です。
戻り値は文字型です。

LISTAGG関数の書式

LISTAGG(連結したい列, 'デリミタ') WITHIN GROUP (ORDER BY 連結順の基準列)

引数	説明
連結したい列	連結にしたい列名を指定します。
デリミタ	連結にする際の区切り記号を指定します。
連結順の基準列	連結するときの順番を決める基準列を指定します。

LISTAGG関数では、指定した列の複数行の値をORDER BYで指定した基準で並び替えて、連結します。

LISTAGG関数の使用例

以下のテーブルを使って「LIATAGG関数」の動作を確認してみましょう。

NAME列の値をID列の順番で連結するSQLを試してみましょう。

SELECT LISTAGG(NAME,',') WITHIN GROUP (ORDER BY ID)
FROM TEST6;

SQLの結果

NAME列の値をDEPARTMENT_ID列の順番で連結するSQLを試してみましょう。

SELECT LISTAGG(NAME,',') WITHIN GROUP (ORDER BY DEPARTMENT_ID)
FROM TEST6;

SQLの結果

COUNTRY_CDごとのNAME列の値を連結するSQLを試してみましょう。

SELECT LISTAGG(NAME,',') WITHIN GROUP (ORDER BY ID)
FROM TEST6
GROUP BY COUNTRY_CD;

SQLの結果

SELECT COUNTRY_CD,LISTAGG(NAME,',') WITHIN GROUP (ORDER BY ID)
FROM TEST6
GROUP BY COUNTRY_CD;

SQLの結果