更新:2024/09/15

MySQLの外部結合について

1. 外部結合とは

外部結合、二つのテーブルを結合する方法の一つです。外部結合は、指定された条件に一致する行を結合するだけでなく、一致しない行も結果に含めます。

  1. 左外部結合(Left Outer Join)左側のテーブルのすべての行と、右側のテーブルの一致する行を結合します。右側のテーブルに一致する行がない場合は、その部分にNULLが入ります。
  2. 右外部結合(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