
おはようございます、未経験からITエンジニア転職を目指すいずみです。
短期集中連載のMySQLシリーズです。前回に引き続き、MySQLの結合について、アウトプットしたいと思います。
一週間で身につくMySQL|トップページ~データベースの初心者でも、気軽にSQLが学習できるサイトです。結合するためテーブル追加
まず、これまで使ってきた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基礎編のラストなので、もう少しお付き合いください!