SQL DML:結合(JOIN)

概要/結合方法

※複数のテーブルをくっつける。
その上でSELECTしたりする。
情報処理資格的に言うと「結合」

(1)テーブルを , で区切り、WHERE句で絞る
SELECT * FROM 表1, 表2
WHERE 表1.列 = 表2.列 AND ~
※WHERE句無しの場合:全組み合わせ表示
(「単純結合」参照)

(2)JOIN句で結合条件を指定
SELECT * FROM テーブル1
JOIN テーブル2 ON テーブル1.列1 = テーブル2.列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 等級
ONBETWEEN 最低 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句

~ FROM (テーブル名1) JOIN (テーブル名2)
USING 列名

↓と同じ
~ FROM (テーブル名1) JOIN (テーブル名2)
ON (テーブル名1).列名 = (テーブル名2).列名

SELECT *
FROM テーブル1 JOIN テーブル2
USING (列1)
※×:NATURAL JOIN ○:JOIN

USING句を使うのに適している場合
(自然結合と比較して)
・結合する表に同名で別型の列があり、自然結合するとエラーになってしまう場合
 エラーとなる列を結合条件から外せる
結合する表に同名でNULL許容の列があり、自然結合するとエラーになってしまう場合

DELETE
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を持たない為、表示されていない。

外部結合演算子(+)

SELECT ~ FROM MYTABLE M, EMPLOYEE E WHERE M.ID = E.ID;
※ ↓と同じ
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
の両方

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
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++のレコードが表示される。

自然結合

NATURAL JOIN
結合条件指定無しの結合(条件を指定できない)
列名、データ型、サイズの列を自動で抽出して結合する。
・結合条件に合致する列が無い場合、単純結合になる。
・結合条件に合致する列が1つの場合、内部結合になる。
・2つ、3つ・・・と自然結合を行う場合、左外部結合(LEFT OUTER JOIN)になる。
・結合条件列が複数ある場合、~ LEFT OUTER JOIN ~ ON ~ AND ~ と同様、結合条件が絞られる。

SELECT *
FROM テーブル1 NATURAL JOIN テーブル2 NATURAL JOIN テーブル3

自己結合

1つの表内で論理的な複数の表を作成した上で、それぞれを結合する結合
※①元テーブル

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列の条件で全件結合した後、男だけに絞る(結合行が多い)

結合した列を更新

UPDATE
(
 SELECT 列 FROM 表1 JOIN 表2 ON ~
)
SET 列 = '値'