MySQLの外部結合について

1. 外部結合とは

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であるため、関連がない４番目と５番目のキャラクターは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までだから当然か