SQL Joins

概要

Inner Join

AとBの共通きょうつうデータ。

SELECT *
FROM A
INNER JOIN B ON A.KEY = B.KEY

Left Join

Aの部分ぶぶんデータのみ（Bとの交差こうさを除のぞく）。

SELECT *
FROM A
LEFT JOIN B ON A.KEY = B.KEY
WHERE B.KEY IS NULL

Right Join

Bの部分ぶぶんデータのみ（Aとの交差こうさを除のぞく）。

SELECT *
FROM A
RIGHT JOIN B ON A.KEY = B.KEY
WHERE A.KEY IS NULL

Full Outer Join

すべてのデータ（A ∪ B）

SELECT *
FROM A
FULL OUTER JOIN B ON A.KEY = B.KEY

AまたはBのみのデータ（交差を除く）

SELECT *
FROM A
FULL OUTER JOIN B ON A.KEY = B.KEY
WHERE A.KEY IS NULL OR B.KEY IS NULL

Natural Join

同おなじ列名れつめいで自動的じどうてきに JOIN を行おこない、ON 条件じょうけんを明示的めいじてきに指定していする必要ひつようがない。

構文

SELECT *
FROM A
NATURAL JOIN B

例

employee と department テーブルを使用しよう（共通列きょうつうれつ：DEPARTMENT_ID）：

SELECT e.FULL_NAME, d.DEPARTMENT_NAME
FROM employee e
NATURAL JOIN department d

Inner Join の等価な書き方

SELECT e.FULL_NAME, d.DEPARTMENT_NAME
FROM employee e
INNER JOIN department d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID

Natural Join は同おなじ名前なまえのすべての列れつを自動的じどうてきにマッチさせるため、予期よきしない結果けっかになる可能性かのうせいがある。ON 条件じょうけんを明示的めいじてきに使用しようすることを推奨すいしょうする。

デモテーブル

department テーブル

DEPARTMENT_ID	DEPARTMENT_NAME
1	Executive
2	HR
3	Sales
4	Development
5	Support
6	Research

employee テーブル

EMPLOYEE_ID	FULL_NAME	DEPARTMENT_ID	ROLE	MANAGER_ID
1	John Smith	1	CEO	(null)
2	Sarah Goodes	1	CFO	1
3	Wayne Ablett	1	CIO	1
4	Michelle Carey	2	HR Manager	1
5	Chris Matthews	3	Sales Manager	2
6	Andrew Judd	4	Development Manager	3
7	Danielle McLeod	5	Support Manager	3
8	Matthew Swan	2	HR Representative	4
9	Stephanie Richardson	2	Salesperson	5
10	Tony Grant	3	Salesperson	5
11	Jenna Lockett	4	Front-End Developer	6
12	Michael Dunstall	4	Back-End Developer	6
13	Jane Voss	4	Back-End Developer	6
14	Anthony Hird	(null)	Support	7
15	Natalie Rocca	5	Support	7

location テーブル

LOCATION_ID	OFFICE_NAME	CITY	COUNTRY
1	Headquarters	New York	USA
2	Main Office	London	UK
3	Sales Office	Tokyo	Japan
4	Development Office	San Francisco	USA
5	Support Office	Sydney	Australia
6	Research Office	Berlin	Germany

3つのテーブルを結合

複数ふくすうテーブルの関連かんれんクエリで、employee、department、location テーブルを結合けつごうする。

シナリオ

従業員じゅうぎょういんの名前なまえ、所属しょぞく部門ぶもん、オフィスの場所ばしょを照会しょうかいする。

例

SELECT
    e.FULL_NAME,
    e.ROLE,
    d.DEPARTMENT_NAME,
    l.OFFICE_NAME,
    l.CITY,
    l.COUNTRY
FROM employee e
INNER JOIN department d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
INNER JOIN location l ON d.DEPARTMENT_ID = l.LOCATION_ID

結果例

FULL_NAME	ROLE	DEPARTMENT_NAME	OFFICE_NAME	CITY	COUNTRY
John Smith	CEO	Executive	Headquarters	New York	USA
Sarah Goodes	CFO	Executive	Headquarters	New York	USA
Wayne Ablett	CIO	Executive	Headquarters	New York	USA
Michelle Carey	HR Manager	HR	Main Office	London	UK
Matthew Swan	HR Representative	HR	Main Office	London	UK
Chris Matthews	Sales Manager	Sales	Sales Office	Tokyo	Japan
Tony Grant	Salesperson	Sales	Sales Office	Tokyo	Japan

LEFT JOIN の使用

部門ぶもんや場所ばしょがない従業員じゅうぎょういんも含ふくめる場合ばあい：

SELECT
    e.FULL_NAME,
    e.ROLE,
    d.DEPARTMENT_NAME,
    l.OFFICE_NAME
FROM employee e
LEFT JOIN department d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
LEFT JOIN location l ON d.DEPARTMENT_ID = l.LOCATION_ID

LEFT JOIN を使用しようする場合ばあい、マッチしない列れつは NULL として表示ひょうじされる。例たとえば Anthony Hird の DEPARTMENT_ID は NULL なので、DEPARTMENT_NAME と OFFICE_NAME は両方りょうほうとも NULL になる。