SQL JOIN大全：内部結合・外部結合・USING句・NATURAL JOIN・自己結合をまとめて解説
内部結合・外部結合・完全外部結合 — 3つのJOINを視覚的に理解する
SQL の JOIN は、複数の表にまたがるデータを結び付けて 1 つの結果として取得するための仕組みです。
特に「共通する列（キー）」を基準に表どうしを関連付けることで、単一の表では得られない情報を取り出すことができます。
JOIN には 内部結合（INNER JOIN）、
片側の表をすべて残す 外部結合（OUTER JOIN）、
両方の表の全件を対象にする 完全外部結合（FULL OUTER JOIN） などがあり、
取得する行の範囲がそれぞれ異なります。
PROD表とCATEGORY表の構造とデータを確認して下さい。
PROD表
| 列名 | NULL? | 型 |
|---|---|---|
| PRODID | NOT NULL | NUMBER(2) |
| NAME | VARCHAR2(20) | |
| CATEGORY | NUMBER(2) | |
| STARTDATE | DATE | |
| ENDDATE | DATE |
PROD_ID PROD_NAME CATEGORY_ID START_DAT END_DATE
---------- -------------------- ----------- --------- ---------
10 APPLE 1 01-JAN-24
11 TV 2 01-OCT-23 01-MAR-24
12 NO_CATEGORY
CATEGORY表（カテゴリ）
| 列名 | NULL? | 型 |
|---|---|---|
| CATEGORY | NOT NULL | NUMBER(2) |
| NAME | VARCHAR2(20) |
CATEGORY_ID CATEGORY_NAME
----------- --------------------
1 FOOD
2 ELECTRONICS
LEFT OUTER（PRODをすべて出す）
LEFT OUTER JOIN は、左側の表（ここでは PROD）を基準にします。
そのため PROD の行はすべて結果に含まれます。
結合条件である CATEGORY_ID が CATEGORY 表に存在する場合は対応する行が結合され、
存在しない場合は CATEGORY 側の列が NULL になります。
SELECT *
FROM PROD P
LEFT JOIN CATEGORY C
ON P.CATEGORY_ID = C.CATEGORY_ID;
実行結果
PROD_ID PROD_NAME CATEGORY_ID START_DATE END_DATE CATEGORY_ID CATEGORY_NAME
------- ------------ ----------- ---------- ---------- ----------- ----------------
10 APPLE 1 01-JAN-24 1 FOOD
11 TV 2 01-OCT-23 01-MAR-24 2 ELECTRONICS
12 NO_CATEGORY (NULL) (NULL) (NULL) (NULL)
RIGHT OUTER（CATEGORYをすべて出す）
RIGHT OUTER JOIN は、右側の表（今回では CATEGORY）を基準にします。
そのため CATEGORY の行はすべて結果に含まれます。
PROD の CATEGORY_ID と一致する行があれば結合され、
一致しない場合は PROD 側の列が NULL になります。
SELECT *
FROM PROD P
RIGHT OUTER JOIN CATEGORY C
ON P.CATEGORY_ID = C.CATEGORY_ID;
実行結果
PROD_ID PROD_NAME CATEGORY_ID START_DATE END_DATE CATEGORY_ID CATEGORY_NAME
-------- ------------ ------------ ----------- ----------- ------------ ----------------
10 APPLE 1 01-JAN-24 1 FOOD
11 TV 2 01-OCT-23 01-MAR-24 2 ELECTRONICS
INNER JOIN
内部結合（INNER JOIN）は、結合条件が一致する行だけを取得する結合です。
両方の表に共通して存在するデータだけが結果に含まれ、一致しない行は結果に含まれません。
SELECT *
FROM PROD P
INNER JOIN CATEGORY C
ON P.CATEGORY_ID = C.CATEGORY_ID;
実行結果
PROD_ID PROD_NAME CATEGORY_ID START_DATE END_DATE CATEGORY_ID CATEGORY_NAME
-------- ------------ ------------ ----------- ----------- ------------ ----------------
10 APPLE 1 01-JAN-24 1 FOOD
11 TV 2 01-OCT-23 01-MAR-24 2 ELECTRONICS
FULL OUTER JOIN
完全外部結合（FULL OUTER JOIN）は、左表と右表のどちらにも存在するすべての行を結果に含める結合です。
PROD と CATEGORY の両方を基準にするため、結合できる行は結合され、
どちらか片方にしか存在しない行も NULL を補ってすべて返されます。
SELECT *
FROM PROD P
FULL OUTER JOIN CATEGORY C
ON P.CATEGORY_ID = C.CATEGORY_ID;
PROD_ID PROD_NAME CATEGORY_ID START_DATE END_DATE CATEGORY_ID CATEGORY_NAME
-------- ------------ ------------ ----------- ---------- ------------ ----------------
10 APPLE 1 01-JAN-24 1 FOOD
11 TV 2 01-OCT-23 01-MAR-24 2 ELECTRONICS
12 NO_CATEGORY (NULL) (NULL) (NULL) (NULL)
今の CATEGORY 表には
「PROD に存在しない CATEGORY」が無いため、
右側 NULL 行は出ていません。
例：CATEGORY に 3 BOOK を入れるとこういう行が出ます↓
NULL NULL NULL NULL NULL 3 BOOK
USING 句とは？
JOIN の基本的な仕組みを理解した後は、
結合条件の書き方の違いにも触れておく必要があります。
SQL では、結合条件の指定方法として ON句 と USING句 の2種類があります。
通常は ON A.col = B.col のように結合条件を明示しますが、
両方の表に「同じ名前」の列が存在する場合、
USING句を使うことでより簡潔に書くことができます。
同じ名前の列で等価結合（=）したいときに使う JOIN の構文
SELECT *
FROM A
JOIN B USING (dept_id);
🔽USING に指定した列はテーブル別名を付けて参照できない
以下は エラーになります：
SELECT A.dept_id FROM A JOIN B USING (dept_id);
USINGは「両方のテーブルに共通の列」として扱うため
別名を付けて参照すると不正扱いになります。
🔽ON句を用いた結合文とは結果が異なる
以下の、A表B表があります。ON句とUSING句を用いた結合結果の違いを見ていきましょう。
A表
EMP_ID DEPT_ID NAME
---------- ---------- --------------------
1 10 AAA
2 10 BBB
B表
DEPT_ID SALARY
---------- ----------
10 500000
USING句を用いた結合
SELECT *
FROM A
JOIN B USING (dept_id);
実行結果
DEPT_ID EMP_ID NAME SALARY
---------- ---------- -------------------- ----------
10 1 AAA 500000
10 2 BBB 500000
ON句を用いた結合
SELECT *
FROM A
JOIN B ON A.dept_id = B.dept_id;
実行結果
EMP_ID DEPT_ID NAME DEPT_ID SALARY
---------- ---------- -------------------- ---------- ----------
1 10 AAA 10 500000
2 10 BBB 10 500000
違いまとめ
| 項目 | ON句 | USING句 |
|---|---|---|
| 表示される列数 | dept_id が2つ表示 | dept_id が1つに統合 |
| 別名で結合列を指定 | OK | NG |
| 同名列が必要か | 必要なし | 同じ名前の列がないと使えない |
自然結合（NATURAL JOIN）とは
USING句の次によく比較されるのが 自然結合（NATURAL JOIN） です。
自然結合は、両方の表にある同名列を自動で結合条件にする仕組みです。
同名の列すべてを自動で結合条件にして等価結合を行うJOIN のことです。
基本構文
SELECT *
FROM A NATURAL JOIN B;
A表
| COLUMN_NAME | DATA_TYPE |
|---|---|
| EMP_ID | NUMBER |
| DEPT_ID | NUMBER |
| NAME | VARCHAR2 |
| EMP_ID | DEPT_ID | NAME |
|---|---|---|
| 1 | 10 | AAA |
| 1 | 20 | AAA |
| 2 | 10 | BBB |
SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'A'
ORDER BY column_id;
| COLUMN_NAME | DATA_TYPE | DATA_LENGTH |
|---|---|---|
| EMP_ID | NUMBER | 22 |
| DEPT_ID | NUMBER | 22 |
| NAME | VARCHAR2 | 20 |
SELECT * FROM B ORDER BY emp_id, dept_id;
| EMP_ID | DEPT_ID | SALARY |
|---|---|---|
| 1 | 10 | 500000 |
| 1 | 20 | 600000 |
| 2 | 10 | 700000 |
SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'B'
ORDER BY column_id;
| COLUMN_NAME | DATA_TYPE | DATA_LENGTH |
|---|---|---|
| EMP_ID | NUMBER | 22 |
| DEPT_ID | NUMBER | 22 |
| SALARY | NUMBER | 22 |
AとBの共通列は
EMP_ID
DEPT_ID
🔹 両方のデータ型も完全一致（NUMBER）
→ 自然結合の対象列はこの2つになる
SELECT *
FROM A JOIN B
ON A.emp_id = B.emp_id
AND A.dept_id = B.dept_id;
EMP_ID DEPT_ID NAME EMP_ID DEPT_ID SALARY
---------- ---------- -------------------- ---------- ---------- ----------
1 10 AAA 1 10 500000
1 20 AAA 1 20 600000
2 10 BBB 2 10 700000
2つの表に同名で異なるデータ型の列がある場合、自然結合を行うとエラーとなります。
※ データ型が異なる場合でも、Oracleの暗黙的なデータ型変換が可能な場合にはエラーなく実行できます。
SQL> DESC departments;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER
DEPARTMENT_NAME VARCHAR2(50)
SQL> DESC departments2;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NOT NULL ROWID
DEPARTMENT_NAME VARCHAR2(50)
SQL> SELECT department_name
FROM departments NATURAL JO 2 IN departments2;
SELECT department_name
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got ROWID
自己結合とは
JOIN は通常、複数の表を結合するときに使用しますが、
実は 1つの表の中だけでも結合を行う場合 があります
同じ表を2つに見立てて、
A表とB表のように扱って結合します。
よくある問い
EMPLOYEE_IDが「1008」の従業員と上司が同じである従業員の名前と、上司のEMPLOYEE_IDを出力します。
どのような問合せを使用するか考えてみましょう
SQL> select * from employees;
EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID
----------- -------------------------------------------------- ----------
1001 BOSS
1010 TOM 1001
1008 MIKE 1010
1011 JOHN 1010
1020 SARA 1010
2000 ALLEN 9999
6 rows selected.
SELECT m.employee_name, e.manager_id
FROM employees e
RIGHT OUTER JOIN employees m
ON e.manager_id = m.manager_id
WHERE e.employee_id = 1008;
WHERE e.employee_id = 1008
→ e を MIKE の1行に固定する
JOIN 条件 e.manager_id = m.manager_id
→ MIKE の上司ID（1010）と同じ manager_id を持つ人を m 側に全部並べる
RIGHT JOIN なので m 側の行を全部残す
→ MIKE / JOHN / SARA の3行が残る
横に並べると以下になる
よくある間違いの選択肢
SELECT e.employee_name, e.manager_id FROM employees e
LEFT OUTER JOIN employees m on (e.employee_id = m.employee_id)
WHERE e.employee_id = 1008;
EMPLOYEE_NAME MANAGER_ID
-------------------------------------------------- ----------
MIKE 1001
なぜ「JOHN」「SARA」が出ない
JOIN条件が「e.manager_id = m.employee_id」なので
→ 上司 TOM（employee_id=1010）1件だけ がヒットします
Discussion