MySQL では2個のテーブルを結合してデータ取得（SELECT）を行うのですが、この時テーブルのカラムを用いて2個のテーブルを連結します。 両方のテーブルにデータが存在するもののみを取得することを 内部結合 といいます。 また、片一方のテーブルにしかデータが存在しないものを取得することを 外部結合 といいます。
テーブルの構造を考える時に、データ項目において性質が同じものはコード化を行い別テーブルにその項目の内容を持つ様にします。 このことをテーブルの正規化をと言いますが、なるべく生のデータを持たない様にします。
例として「個人」のデータ（個人テーブル）を持つ場合、情報として名前、性別、結婚状態、生年月日などがあります。 名前や生年月日は個人ごとに異なるので個人テーブルにそのままデータを持ちます。
ただし、「性別」としては「男性」「女性」「その他」「未回答」などがあると思いますが、これらをそのままの文字列で持つと無駄だったりしますので、 「性別」テーブルを別にコード化して持ちます。
また、「結婚状態」では「独身」「既婚」「離婚・死別」などががありますので、「結婚状態」テーブルを持ちます。
とにかく、テストを示すテーブルが無いことには始まりませんので、以下の3個のテーブル「個人」「性別」「結婚状態」を以下の SQL で作成します。
-- 個人テーブル
CREATE TABLE person (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(32),
id_mrg INT,
id_gnd INT
);
-- 既婚状態テーブル
CREATE TABLE marriage (
id INT NOT NULL PRIMARY KEY,
val VARCHAR(32)
);
-- 性別状態テーブル
CREATE TABLE gender (
id INT NOT NULL PRIMARY KEY,
val VARCHAR(32)
);
それでは、上記のテーブルに以下の SQL でテストデータを登録します。
-- 既婚状態テーブル
INSERT INTO marriage(id, val) VALUES(1, '独身') , (2, '既婚'), (3, '離婚'), (4, '死別');
-- 性別状態テーブル
INSERT INTO gender(id, val) VALUES(1, '男性') , (2, '女性'), (3, 'その他'), (4, '未回答');
-- 個人テーブル
INSERT INTO person(name, id_mrg, id_gnd) VALUES
('田中 太郎', 1, 1),
('山田 一郎', 2, 1),
('青木 花子', 2, 2),
('加藤 清正', 3, 1),
('斎藤 優子', 4, 2),
('石田 次郎', 2, 3),
('伊藤 啓治', NULL, 4),
('山下 幸一', 1, NULL);
登録されたデータの確認を行います。
MariaDB [test]> SELECT * FROM marriage; +----+------+ | id | val | +----+------+ | 1 | 独身 | | 2 | 既婚 | | 3 | 離婚 | | 4 | 死別 | +----+------+ 4 rows in set (0.00 sec) MariaDB [test]> SELECT * FROM gender; +----+--------+ | id | val | +----+--------+ | 1 | 男性 | | 2 | 女性 | | 3 | その他 | | 4 | 未回答 | +----+--------+ 4 rows in set (0.00 sec) MariaDB [test]> SELECT * FROM person; +----+------------+--------+--------+ | id | name | id_mrg | id_gnd | +----+------------+--------+--------+ | 1 | 田中 太郎 | 1 | 1 | | 2 | 山田 一郎 | 2 | 1 | | 3 | 青木 花子 | 2 | 2 | | 4 | 加藤 清正 | 3 | 1 | | 5 | 斎藤 優子 | 4 | 2 | | 6 | 石田 次郎 | 2 | 3 | | 7 | 伊藤 啓治 | NULL | 4 | | 8 | 山下 幸一 | 1 | NULL | +----+------------+--------+--------+ 8 rows in set (0.00 sec)
■[内部結合（INNER JOIN）]の例
内部結合（INNER JOIN）の使い方ですが、以下の様な感じで行います。
-- [table1]と[table2]の結合 SELECT table1.カラムリスト [,table2.カラムリスト] FROM table1 INNER JOIN table2 ON table1.[table2のidと紐づくカラム] = table2.id;
内部結合（INNER JOIN）を使って「個人」テーブルの一覧を取得します。
SELECT person.id, person.name, marriage.val AS '婚姻情報', gender.val AS '性別情報' FROM person INNER JOIN marriage ON person.id_mrg = marriage.id INNER JOIN gender ON person.id_gnd = gender.id;
この SQL の結果は以下の様になります。
+----+------------+----------+----------+ | id | name | 婚姻情報 | 性別情報 | +----+------------+----------+----------+ | 1 | 田中 太郎 | 独身 | 男性 | | 2 | 山田 一郎 | 既婚 | 男性 | | 4 | 加藤 清正 | 離婚 | 男性 | | 3 | 青木 花子 | 既婚 | 女性 | | 5 | 斎藤 優子 | 死別 | 女性 | | 6 | 石田 次郎 | 既婚 | その他 | +----+------------+----------+----------+ 6 rows in set (0.00 sec)
内部結合（INNER JOIN） では結合に合致するものしか取得できないので、person の id「7、8」は一覧に表れてきません。
id「7」の「id_mrg」および、id「8」の「id_gnd」は NULL のため結合できません。
それでは、person の id「7、8」について一覧に表示できる様に、外部結合（LEFT JOIN） で行ってみます。
■[外部結合（LEFT JOIN）]の例
内部結合（INNER JOIN） の SQL の INNER を LEFT に替えます。
SELECT person.id, person.name, marriage.val AS '婚姻情報', gender.val AS '性別情報' FROM person LEFT JOIN marriage ON person.id_mrg = marriage.id LEFT JOIN gender ON person.id_gnd = gender.id;
この SQL の結果は以下の様になります。
+----+------------+----------+----------+ | id | name | 婚姻情報 | 性別情報 | +----+------------+----------+----------+ | 1 | 田中 太郎 | 独身 | 男性 | | 2 | 山田 一郎 | 既婚 | 男性 | | 3 | 青木 花子 | 既婚 | 女性 | | 4 | 加藤 清正 | 離婚 | 男性 | | 5 | 斎藤 優子 | 死別 | 女性 | | 6 | 石田 次郎 | 既婚 | その他 | | 7 | 伊藤 啓治 | NULL | 未回答 | | 8 | 山下 幸一 | 独身 | NULL | +----+------------+----------+----------+ 8 rows in set (0.00 sec)
「person.id = 7」の「person.id_mrg」が NULL で、 「person.id = 8」の「person.id_gnd」が NULL のため、 それぞれ連結しているテーブルに該当するレコードがないので、一覧では NULL と表示されます。
今回は結合の例なので JOIN を使いましたが、以下の様な SQL で内部結合と同様な結果が得られます。
SELECT person.id, person.name, marriage.val AS '婚姻情報', gender.val AS '性別情報' FROM person, marriage, gender WHERE person.id_mrg = marriage.id AND person.id_gnd = gender.id;
また、外部結合と同様な結果を取得できる以下の様な SQL もあります。 SELECT 句の中で直接それぞれに対応するテーブルと結合する方式です。
外部結合が嫌いであればこちらの方式でもよいと思います。
SELECT person.id, person.name, ( SELECT val FROM marriage WHERE person.id_mrg = marriage.id ) AS '婚姻情報', ( SELECT val FROM gender WHERE person.id_gnd = gender.id ) AS '性別情報' FROM person;
コメント