データベース・SQL

内部結合(INNER JOIN)と外部結合(OUTER JOIN)

【前提】データ準備

以下のclubs(部活)テーブルとstudents(生徒)テーブルがあるとして話をします。

students(生徒)テーブルは所属するclubsのidを外部キーとして持っていて、次のレコードがある。

  • id: 5「美術部」に所属する生徒はいない
  • id:8 「帰宅花子」はどの部活にも所属していない(帰宅部)

clubs
id name
1 サッカー部
2 野球部
3 吹奏楽部
4 美術部
5 陸上部
students
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;

は、部活に所属していない「帰宅花子」も取得される。

-データベース・SQL

© 2021 フリエン生活 Powered by AFFINGER5