1週間で身に付くMySQL #5

Pocket

おはようございます、未経験からITエンジニア転職を目指すいずみです。

短期集中連載のMySQLシリーズです。前回に引き続き、MySQLの結合について、アウトプットしたいと思います。

一週間で身につくMySQL|トップページ～データベースの初心者でも、気軽にSQLが学習できるサイトです。

Contents

1 結合するためテーブル追加
2 テーブル同士を結合
3 内部結合 – INNER JOIN –
4 内部結合と検索
5 交差結合 – CROSS JOIN –
6 まとめ

結合するためテーブル追加

まず、これまで使ってきたcastsテーブルにALTER文とUPDATE文を用いて、カラムを追加します。

追加するカラムは、①キャストの役名と②それぞれの属する派閥コードとします。

# カラムを追加する
## role : 鎌倉殿の役柄
ALTER TABLE casts ADD role varchar(128) NULL;

## faction : 役柄の派閥を表す4文字のコード
ALTER TABLE casts ADD faction char(4) NULL;

# すでに登録済のレコードに追加カラムの情報を追記する
UPDATE casts SET role = "源　頼朝", faction = "kama" WHERE id = 1;
UPDATE casts SET role = "北条　義時", faction = "hojo" WHERE id = 2;
UPDATE casts SET role = "北条　政子", faction = "hojo" WHERE id = 3;
UPDATE casts SET role = "八重", faction = "hojo" WHERE id = 4;
UPDATE casts SET role = "源　義経", faction = "kama" WHERE id = 5;
UPDATE casts SET role = "三浦　義村", faction = "band" WHERE id = 6;
UPDATE casts SET role = "上総　広常", faction = "band" WHERE id = 7;
UPDATE casts SET role = "北条　時政", faction = "hojo" WHERE id = 8;
UPDATE casts SET role = "実衣", faction = "hojo" WHERE id = 9;
UPDATE casts SET role = "善児", faction = "band" WHERE id = 10;

三浦義村や上総広常は、別々の御家人ですが、便宜上”坂東武者”としてまとめてしまいました。ミーハーなので、史実とそぐわないなどのご意見はご容赦ください、、、

次に、派閥コード(faction_name)のテーブルを生成します。

# faction_name : 派閥の名前を格納するテーブル
CREATE TABLE faction_name(
	faction		char(4) NOT NULL,
	name		char(4) NOT NULL
);

# faction_nameテーブルにレコードを挿入
INSERT INTO faction_name VALUES('kama', '鎌倉家');
INSERT INTO faction_name VALUES('hojo', '北条家');
INSERT INTO faction_name VALUES('band', '坂東武者');

無事追加できました！

テーブル同士を結合

まずは、先ほど更新した新キャストテーブルを見てみましょう。

役の所属する派閥(善児のfactionは”band”だけどどこだ？)がコードで表されているので、パッとみてどこの所属か分かりづらいですね。

こんなとき2つのテーブル(①casts ②faction_name)を結合して、より見やすくしましょう。

内部結合 – INNER JOIN –

それぞれのテーブルの共通する外部キーを指定して、結合することを“内部結合”といいます。

# ①casts ②faction_nameというテーブルがあったとする。
# ①と②を"faction"というカラムをキーとして、結合する。
SELECT * FROM casts INNER JOIN faction_name ON casts.faction = faction_name.faction;

castsテーブルとfaction_nameテーブルを共通の外部キー”faction”を使って結合しています。

処理の流れは、、、

castsテーブルの最初のレコード(源頼朝)のfactionカラム(kama)に対し、faction_nameテーブルのfactionカラムに同じ値を持つか調べる。
存在した場合は、それぞれのテーブルの「factionが一致したレコード」を結合し、新たなレコードとして返します。

を繰り返しています。もし1.で一致するレコードがなかった場合は、そのレコードは結果に反映されません。

次に構文を噛み砕いてみましょう。

# 結合の全容
SELECT * FROM casts INNER JOIN faction_name ON casts.faction = faction_name.faction;

# castsテーブルとfaction_nameテーブルでINNER JOIN(内部結合)するぜという宣言
SELECT * FROM casts INNER JOIN faction_name ON ~

まず前半では、“INNER JOIN ~ ON”で内部結合するぜと宣言しています。

ONの後に、結合に用いる外部キーのカラムを指定します。

# 結合対象の外部キーを指定
SELECT ~ ON casts.faction = faction_name.faction;

つまり、castsテーブルの”faction”カラムとfaction_nameテーブルの”faction”カラムが一致すれば、結合するということです。

今回外部キーとして利用するカラム名が、どちらも”faction”で同一のため、以下のように簡略化することもできるようです。

# 外部キーのカラム名が同一のとき、USINGを用いて簡略化できる
SELECT * FROM casts INNER JOIN faction_name USING(faction);

内部結合と検索

先ほどの単元で内部結合して生成されたテーブルを見てみましょう。

結合に用いたカラム(faction)がそのまま書かれていて、若干読み取りづらいので、改善してみます。

ここでポイントとなるのは、内部結合された2つのテーブルのカラムを指定するときは、「テーブル名.カラム名」と記載すること。

この記法を用いて、見やすく検索してみましょう！

# 内部結合を用いて2つのテーブルを見やすくする
SELECT id, role, faction_name.name, casts.name, affiliation, age, sex FROM casts INNER JOIN faction_name USING(faction);

いかがでしょうか！鎌倉殿キャストということなので、まず役柄と派閥を持ってきて、そのあと俳優の情報を並べてみました。

それぞれのテーブルに、”name”というカラムが重複しているので、前述の「クラス名.カラム名」という記述で区別しています。

さらに言うと、内部結合したテーブルに対して、WHERE文を当てることも可能です。

# 50歳以上の俳優
SELECT id, role, faction_name.name, casts.name, affiliation, age, sex FROM casts INNER JOIN faction_name USING(faction) WHERE age > 50;

上総介は格好良かったです…

交差結合 – CROSS JOIN –

内部結合の他に、もう一つの結合を紹介します。

交差結合といって「2つのテーブル組み合わせの全てを作る結合」のことです。

# 交差結合
SELECT * FROM casts CROSS JOIN faction_name;

単純に2つのテーブルが組み合わせたものが出力されました。

WHERE文をつけて条件の絞り込みを行えば、先に示した内部結合と同様の結果を得ることができます。

ただし、、、それぞれcastsが10行、faction_nameが3行あることから、組み合わせは30行にもなります。

これが問題点で、結合の結果が膨大になってしまい、使い方を誤るとデータベースの検索スピードを著しく低下させる可能性があるのです。

まとめ

今回は、内部結合と交差結合についてアウトプットしてみました。

交差結合は結果が膨大になるという課題を抱えているので、一般的には「内部結合」と次回紹介する「外部結合」が用いられているようです。

次回がMySQL基礎編のラストなので、もう少しお付き合いください！