SQL DML：結合(JOIN)
概要/結合方法
※複数のテーブルをくっつける。
その上でSELECTしたりする。
情報処理資格的に言うと「結合」
(1)テーブルを , で区切り、WHERE句で絞る
SELECT * FROM 表1, 表2
WHERE 表1.列 = 表2.列 AND ～
※WHERE句無しの場合：全組み合わせ表示
(「単純結合」参照)
(2)JOIN句で結合条件を指定
SELECT * FROM テーブル1
JOIN テーブル2 ON テーブル1.列１ = テーブル2.列２
等価結合_非等価結合
等価結合
自然結合、ON句、USING句を使用した結合時の条件に「=」を用いて列指定を行う結合。
非等価結合
ON句を使用した結合時の条件に「=」を用いない列指定を行う結合。
BETWEEN、IN、LIKE等のWHERE句に用いられる検索条件を利用できる。
特定の範囲で条件を指定する結合方法
給与テーブル
氏名 給与 級
伊藤 180,000 ?
田中 195,000 ?
白井 260,000 ?
等級テーブル
級 最低 最高
3 150,001 200,000
4 200,001 300,000
5 300,001 400,000
SELECT *
FROM 給与 JOIN 等級
ON 級 BETWEEN 最低 AND 最高
氏名 給与 等級
伊藤 180,000 3
田中 195,000 4
白井 260,000 4
内部結合
| 結合対象 列名 |
結合対象 列型 |
複数列 | 結合列の SELECT |
メリット | |
|---|---|---|---|---|---|
| NATURAL JOIN |
別名称× | 別型× (暗黙の型変換) |
強制AND条件 | 列修飾× | 記述が楽 |
| JOIN USING |
別名称× | 別型× (暗黙の型変換) |
条件指定○ | 列修飾× | 表示列を減らせる |
| JOIN ON |
別名称○ | 別型△ (明示的型変換) |
条件指定○ | 列修飾○ | 正確 |
INNER JOIN
結合対象の表(テーブル)、両方に共通するレコードのみを結合・抽出する結合方法
SELECT 生徒.*, テスト.*
FROM 生徒 INNER JOIN テスト
FROM 生徒 JOIN テスト ※INNERは省略可
ON 生徒.ID = テスト.ID
| EMPNO | ENAME | DEPTNO |
| 1 | C# | 1 |
| 2 | C++ | 2 |
| 3 | VB.NET | 1 |
| 4 | VB6 | 1 |
| 5 | JAVA | 2 |
| 6 | JS | 3 |
| 7 | PERL | 3 |
↑↓を結合
| DEPTNO | DNAME | BOSS |
| 1 | MicroSoft | 1 |
| 2 | Almighty | 2 |
| 3 | WEB | 3 |
↓ 結果
| EMPNO | ENAME | DEPTNO | DNAME |
| 1 | C# | 1 | MicroSoft |
| 4 | VB6 | 1 | MicroSoft |
| 3 | VB.NET | 1 | MicroSoft |
| 5 | JAVA | 2 | Almighty |
| 2 | C++ | 2 | Almighty |
| 7 | PERL | 3 | WEB |
| 6 | JS | 3 | WEB |
USING句
USING 列名
↓と同じ
～ FROM (テーブル名1) JOIN (テーブル名2)
ON (テーブル名1).列名 = (テーブル名2).列名
SELECT *
FROM テーブル1 JOIN テーブル2
USING (列1)
※×：NATURAL JOIN ○：JOIN
USING句を使うのに適している場合
(自然結合と比較して)
・結合する表に同名で別型の列があり、自然結合するとエラーになってしまう場合
エラーとなる列を結合条件から外せる
・結合する表に同名でNULL許容の列があり、自然結合するとエラーになってしまう場合
FROM 表1
USING 表2
WHERE
表2 ．列 = 表1 ．列 --結合条件
AND ～ --条件他
外部結合
結合条件に一致するデータに加えて、結合対象の表(テーブル)、どちらか片方にだけあるレコードも抽出・結合する結合方法
このどちらか片方にだけある表の指定によって、
・LEFT OUTER JOIN
・RIGHT OUTER JOIN
のやり方がある。
※結合前テーブル
| EMPNO | ENAME | DEPTNO |
| 1 | C# | 1 |
| 2 | C++ | (NULL) |
| 3 | VB.NET | 1 |
| 4 | VB6 | 1 |
| 5 | JAVA | 2 |
| 6 | JS | 3 |
| 7 | PERL | 3 |
| DEPTNO | DNAME |
| 1 | MicroSoft |
| 2 | Almighty |
| 3 | WEB |
| 4 | Assembly |
左外部結合
SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMPLOYEE E LEFT OUTER JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO
ORDER BY E.EMPNO;
| EMPNO | ENAME | DEPTNO | DNAME |
| 1 | C# | 1 | MicroSoft |
| 2 | C++ | (NULL) | (NULL) |
| 3 | VB.NET | 1 | MicroSoft |
| 4 | VB6 | 1 | MicroSoft |
| 5 | JAVA | 2 | Almighty |
| 6 | JS | 3 | WEB |
| 7 | PERL | 3 | WEB |
※左の表：EMPLOYEEのC++におけるDEPTNOは(NULL)で、
表：DEPARTMENTに該当するレコードは無いが、
結合後の表のDNAMEも(NULL)として表示している。
＝左表のレコードは全て表示されている一方、右表のAssemblyのレコードは表示されていない。
右外部結合
SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO
ORDER BY E.EMPNO;
| EMPNO | ENAME | DEPTNO | DNAME |
| 1 | C# | 1 | MicroSoft |
| 3 | VB.NET | 1 | MicroSoft |
| 4 | VB6 | 1 | MicroSoft |
| 5 | JAVA | 2 | Almighty |
| 6 | JS | 3 | WEB |
| 7 | PERL | 3 | WEB |
| (NULL) | (NULL) | 4 | Asesembly |
※左の列：EMPLOYEEにおいて、
右の表：DEPARTMENTのAssemblyをDEPTNO列に持つレコードは無いが、
結合後の表にはAssemblyレコードが表示されている。
＝右表のレコードは全て表示されている一方、
左表のC++のレコードはDEPTNOを持たない為、表示されていない。
外部結合演算子(+)
※ ↓と同じ
FROM MYTABLE M INNER JOIN EMPLOYEE E ON M.ID = E.ID;
SELECT ～ FROM MYTABLE M, EMPLOYEE E WHERE M.ID(+) = E.ID;
※ ↓と同じ
FROM MYTABLE M RIGHT OUTER JOIN EMPLOYEE E ON M.ID = E.ID;
SELECT ～ FROM MYTABLE M, EMPLOYEE E WHERE M.ID = E.ID(+);
※ ↓と同じ
FROM MYTABLE M LEFT OUTER JOIN EMPLOYEE E ON M.ID = E.ID;
完全外部結合
・LEFT OUTER JOIN
・RIGHT OUTER JOIN
の両方
FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO
| EMPNO | ENAME | DEPTNO | DNAME |
| 1 | C# | 1 | MicroSoft |
| 2 | C++ | (NULL) | (NULL) |
| 3 | VB.NET | 1 | MicroSoft |
| 4 | VB6 | 1 | MicroSoft |
| 5 | JAVA | 2 | Almighty |
| 6 | JS | 3 | WEB |
| 7 | PERL | 3 | WEB |
| (NULL) | (NULL) | 4 | Asesembly |
左外部結合では表示されなかったAssemblyのレコードが、
右外部結合では表示されなかったC++のレコードが表示される。
自然結合
自己結合
１つの表内で論理的な複数の表を作成した上で、それぞれを結合する結合
※①元テーブル
| EMPNO | ENAME | BOSS |
| 1 | C# | 1 |
| 2 | C++ | 2 |
| 3 | VB.NET | 1 |
| 4 | VB6 | 1 |
| 5 | JAVA | 2 |
| 6 | JS | 7 |
| 7 | PERL | 7 |
※②元テーブル内のBOSSを抽出したテーブル
| EMPNO | ENAME |
| 1 | C# |
| 2 | C++ |
| 7 | PERL |
※①と②をBOSS = EMPNOで結合
SELECT E.EMPNO, E.ENAME, E.BOSS, B.ENAME BOSSNAME
FROM EMPLOYEE E LEFT OUTER JOIN EMPLOYEE B
ON E.BOSS = B.EMPNO
| EMPNO | ENAME | BOSS | BOSSNAME |
| 1 | C# | 1 | C# |
| 2 | C++ | 2 | C++ |
| 3 | VB.NET | 1 | C# |
| 4 | VB6 | 1 | C# |
| 5 | JAVA | 2 | C++ |
| 6 | JS | 7 | PERL |
| 7 | PERL | 7 | PERL |
単純結合
別名：直積結合/クロス結合
デカルト積(直積)が生成される。
SELECT *
FROM テーブル1 CROSS JOIN テーブル1
↓と同じ
(1)SELECT * FROM テーブル1, テーブル2
(2)SELECT * FROM テーブル1 NATURAL JOIN テーブル2
※共通列無しの場合
(3)SELECT * FROM テーブル1 (INNER) JOIN テーブル2
※結合条件無し
(4)SELECT * FROM テーブル1 (INNER) JOIN テーブル2
ON テーブル1.列 = テーブル1.列
※無意味な結合条件
速度改善
SELECT * FROM テスト JOIN (SELECT * 生徒 WHERE 性別 = 男) AS 男生徒
ON 男生徒.NO = テスト.NO
※男だけに絞った生徒テーブルとテストをNO列の条件で結合(結合行が少ない)
SELECT * FROM テスト JOIN 生徒
ON 生徒.NO = テスト.NO
WHERE 性別 = 男
※両テーブルにおけるNO列の条件で全件結合した後、男だけに絞る(結合行が多い)
結合した列を更新
(
SELECT 列 FROM 表1 JOIN 表2 ON ～
)
SET 列 = '値'
