MySQLで外部結合と内部結合1～他テーブルのフィールド情報変更に対応する～ - nicepaper @ ウィキ

データベースにおいて、他のテーブルの情報が変更された時にそのテーブルの情報も一緒に取得して、結合したい時があります。そんなときにテーブル同士の結合について 考えていきたいと思います。10月14日記事

目次

---

なぜ外部結合や内部結合が必要か。

フィールドの中身の内容が変更されないような場合は特には考えなくていいですが、変更される恐れがある場合は外部結合や内部結合によって対応できるようにします。

使用例

ユーザが投稿した情報一覧が表示される仕組みにおいて、ユーザの名前（ニックネームやHN）が変更された場合にも、対応できるようにする。

失敗の具体例

例えば、アカウントテーブル（account）と記事テーブル(article)というものがあったとします。

account

account_id	accound_hn	account_email	account_passwordsalt
1	田中太郎	[e-mail]	0495ab094ef・・・・
2	山田花子	[e-mail]	0495ab094ef・・・・
3	鈴木次郎	[e-mail]	0495ab094ef・・・・

テーブル作成のクエリ

CREATE TABLE account(
account_id INT NOT NULL AUTO_INCREMENT,
account_hn VARCHAR(255) NOT NULL,
account_email VARCHAR(255) NOT NULL,
account_passwordsalt VARCHAR(255) NOT NULL,
PRIMARY KEY(account_id)
);

そしてデータ登録

INSERT INTO account (account_hn,account_email,account_passwordsalt)VALUES('田中太郎','[e-mail]','0495ab094ef・・・・');
INSERT INTO account (account_hn,account_email,account_passwordsalt)VALUES('山田花子','[e-mail]','0495ab094ef・・・・');
INSERT INTO account (account_hn,account_email,account_passwordsalt)VALUES('鈴木次郎','[e-mail]','0495ab094ef・・・・');

そして、記事テーブル作成
article

article_id	article_authorname	article_title	article_comment	status
1	田中太郎	タイトル1	コメント1	1
2	田中太郎	タイトル2	コメント2	2
3	鈴木次郎	タイトル3	コメント3	1

※statusの1は公開状態を表す。2は削除状態を表す。

このような場合、 accountテーブルにて、ユーザの名前（ニックネームやHN)が変更された場合、articleテーブルのarticle_authornameの反映がめんどくさくなります。 このようなことを防ぐために、変わった場合にも反映できるような仕組みにしていくために外部結合や内部結合が使われます。

テーブルの設計変更

テーブル設計を変更していきます。 article

article_id	article_author_id	article_title	article_comment	status
1	1	タイトル1	コメント1	1
2	1	タイトル2	コメント2	2
3	3	タイトル3	コメント3	1

のようにidで指定していきます。

よってarticle_テーブル作成のクエリは

CREATE TABLE article(
article_id INT NOT NULL AUTO_INCREMENT,
article_author_id INT NOT NULL,
article_title VARCHAR(255) NOT NULL,
article_comment text NOT NULL,
article_status TINYINT NOT NULL,
PRIMARY KEY(article_id)
);

というように変更します。データ登録は

INSERT INTO article (article_author_id,article_title,article_comment,article_status) VALUES (1,'タイトル1','コメント1',1);
INSERT INTO article (article_author_id,article_title,article_comment,article_status) VALUES (1,'タイトル2','コメント2',2);
INSERT INTO article (article_author_id,article_title,article_comment,article_status) VALUES (3,'タイトル3','コメント3',1);

というように登録しておきます。

結合するクエリ

外部結合

SELECT * FROM article LEFT JOIN account ON account.account_id = article.article_author_id;

また、記事が公開中の状態のみ取得したい場合は

SELECT * FROM article LEFT JOIN account ON account.account_id = article.article_author_id WHERE article_status = 1;

内部結合

SELECT * FROM article JOIN account ON account.account_id = article.article_author_id;

また、記事が公開中の状態のみ取得したい場合は

SELECT * FROM article JOIN account ON account.account_id = article.article_author_id WHERE article_status = 1;

似たような感じですが、次のものを発行してみてください。

SELECT * FROM account LEFT JOIN article ON account.account_id = article.article_author_id;
SELECT * FROM account JOIN article ON account.account_id = article.article_author_id;

山田花子さんがNULLとしてフィールドが作られている場合とそうでない場合に分けられると思います。 内部結合は「両方にレコード（フィールド）がないとレコードとして作成しない」 というもので、 外部結合は「片方に存在していなくてもレコード（フィールド）を作成する」 というものです。

田中太郎さんを変更する。

UPDATE account SET account_hn = 'たなかたろー' WHERE account_id = 1;

これで、再び外部結合や内部結合のクエリを投げても反映されていることを確認できると思います。

まとめ：どっち使えばいいの？

基本は内部結合でいいですが、外部結合において、集計0件になる場合に必要になってきます。

以上