MySQLの外部結合について
目次
1. 外部結合とは
外部結合、二つのテーブルを結合する方法の一つです。外部結合は、指定された条件に一致する行を結合するだけでなく、一致しない行も結果に含めます。
- 左外部結合(Left Outer Join)左側のテーブルのすべての行と、右側のテーブルの一致する行を結合します。右側のテーブルに一致する行がない場合は、その部分にNULLが入ります。
- 右外部結合(Right Outer Join)右側のテーブルのすべての行と、左側のテーブルの一致する行を結合します。左側のテーブルに一致する行がない場合は、その部分にNULLが入ります。
2. 実際に試してみる
2.1. 1. テーブルの作成
-- キャラクターテーブルの作成
CREATE TABLE characters (
character_id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);
-- スキルテーブルの作成
CREATE TABLE skills (
skill_id INTEGER PRIMARY KEY,
skill_name TEXT,
description TEXT
);
-- キャラクターとスキルの関連テーブルの作成
CREATE TABLE character_skills (
character_id INTEGER,
skill_id INTEGER,
FOREIGN KEY (character_id) REFERENCES characters(character_id),
FOREIGN KEY (skill_id) REFERENCES skills(skill_id)
);
-- キャラクターデータの挿入
INSERT INTO characters (character_id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO characters (character_id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO characters (character_id, name, age) VALUES (3, 'Charlie', 35);
INSERT INTO characters (character_id, name, age) VALUES (4, 'fuxyuka', 1500);
INSERT INTO characters (character_id, name, age) VALUES (5, 'haruka', 19);
-- スキルデータの挿入
INSERT INTO skills (skill_id, skill_name, description) VALUES (1, 'Magic', 'Ability to cast spells');
INSERT INTO skills (skill_id, skill_name, description) VALUES (2, 'Swordsmanship', 'Expert in using a sword');
INSERT INTO skills (skill_id, skill_name, description) VALUES (3, 'Archery', 'Skilled in using a bow and arrow');
-- キャラクタースキル関連データの挿入
INSERT INTO character_skills (character_id, skill_id) VALUES (1, 1);
INSERT INTO character_skills (character_id, skill_id) VALUES (1, 3);
INSERT INTO character_skills (character_id, skill_id) VALUES (2, 2);
INSERT INTO character_skills (character_id, skill_id) VALUES (3, 1);
INSERT INTO character_skills (character_id, skill_id) VALUES (3, 2);
INSERT INTO character_skills (character_id, skill_id) VALUES (3, 3);
2.2. 2. 外部結合の実行
データベースの関係は次のようになっています。
とりあえずcharactersテーブルの中身を見てみましょう。
SELECT *
FROM characters

はるか
あっ、お姉ちゃんの年齢が。

ふゅか
えっ。
SELECT *
FROM character_skills

はるか
characterのidが1から3までしかスキルが登録されていないのか。
SELECT *
FROM skills;

ふゅか
スキルは3つあります♪
2.3. 左側結合
charactersのidを基準にして外部結合が行われる。charactersのidは1~5であるため、関連がない4番目と5番目のキャラクターはnullとして表示される。
SELECT *
FROM characters
LEFT OUTER JOIN character_skills ON
characters.character_id = character_skills.character_id

はるか
スキルは登録されていないがnullとして表示されたな。
2.4. 右側結合
character_skillsのidを基準にして外部結合が行われる。character_skillsのidは1~3しかないため、idが4や5のキャラクターは表示されない。
SELECT *
FROM characters
RIGHT OUTER JOIN character_skills ON
characters.character_id = character_skills.character_id

はるか
私たちが表示されていないな
3. 外部結合と内部結合の違い
3.1. 内部結合
キャラクターのidを規準にして内部結合を行う。
-- キャラクターとスキルの内部結合
SELECT *
FROM characters
INNER JOIN character_skills on characters.character_id = character_skills.character_id
INNER JOIN skills on skills.skill_id = character_skills.skill_id
3.2. 外部結合
charactersのidを基準にして外部結合を行う。
SELECT *
FROM characters
LEFT OUTER JOIN character_skills ON characters.character_id = character_skills.character_id
LEFT OUTER JOIN skills ON skills.skill_id = character_skills.skill_id;

はるか
関連がないから私たちがnullになるのは当然か

ふゅか
でも、右側結合を行ったらどうなるかな?
SELECT *
FROM characters
RIGHT OUTER JOIN character_skills ON characters.character_id = character_skills.character_id
RIGHT OUTER JOIN skills ON skills.skill_id = character_skills.skill_id;

はるか
内部結合と同じ結果にはなったか。

はるか
でも、character_skillsにあるcharactersのidが1から3までだから当然か
PR