【前提】データ準備
以下のclubs(部活)テーブルとstudents(生徒)テーブルがあるとして話をします。
students(生徒)テーブルは所属するclubsのidを外部キーとして持っていて、次のレコードがある。
- id: 5「美術部」に所属する生徒はいない
- id:8 「帰宅花子」はどの部活にも所属していない(帰宅部)
id | name |
1 | サッカー部 |
2 | 野球部 |
3 | 吹奏楽部 |
4 | 美術部 |
5 | 陸上部 |
id | last_name | first_name | club_id |
1 | 田中 | 英治 | 1 |
2 | 佐藤 | 浩二 | 1 |
3 | 中村 | 俊二 | 1 |
4 | 山田 | 太郎 | 2 |
5 | 鈴木 | 次郎 | 2 |
6 | 佐藤 | 博士 | 3 |
7 | 杉本 | 優子 | 5 |
8 | 帰宅 | 花子 | NULL |
作成するSQLは
-- clubs(部活)テーブル CREATE TABLE clubs ( id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL ); INSERT INTO clubs (name) VALUES ('サッカー部'), ('野球部'), ('吹奏楽部'), ('美術部'), ('陸上部'); -- students(生徒)テーブル CREATE TABLE students ( id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(255) NOT NULL, first_name VARCHAR(255) NOT NULL, club_id BIGINT ); INSERT INTO students (last_name, first_name, club_id) VALUES ('田中', '英治', 1), ('佐藤', '浩二', 1), ('中村', '俊二', 1), ('山田', '太郎', 2), ('鈴木', '次郎', 2), ('佐藤', '博士', 3), ('杉本', '優子', 5), ('帰宅', '花子', NULL);
内部結合(INNER JOIN)
2つのテーブルで一致するレコードのみを取得しするのが内部結合。
SELECT * FROM clubs INNER JOIN students ON clubs.id = students.club_id;
は、部員がいない「美術部」と帰宅部の「帰宅花子」のデータは取得されない。
外部結合(OUTER JOIN)
どちらかのテーブルにしか存在しないものに関しても取得するのが外部結合。
外部結合には左外部結合(LEFT OUTER JOIN
) と右外部結合 (RIGHT OUTER JOIN
) がある。
- 左外部結合(
LEFT OUTER JOIN
)は左側のテーブルを基準にして外部結合を行う方法 - 右外部結合 (
RIGHT OUTER JOIN
)は右側のテーブルを基準にして外部結合を行う方法
左外部結合(LEFT OUTER JOIN)
左側のテーブルを基準にして外部結合を行うので
SELECT * FROM clubs LEFT OUTER JOIN students ON clubs.id = students.club_id;
は、部員のいない「美術部」も取得される。
右外部結合(RIGHT OUTER JOIN)
左側のテーブルを基準にして外部結合を行うので
SELECT * FROM clubs RIGHT OUTER JOIN students ON clubs.id = students.club_id;
は、部活に所属していない「帰宅花子」も取得される。