テーブルを分けた瞬間から、SQL は「1つの表を読む」道具ではなくなります。
前の回で、テーブルを作って SELECT しました。ただ、実際のデータは1つの表に収まりません。タスクと利用者、注文と商品。分けて持つのが正しい設計で、そのぶん取り出すときに繋ぎ直す必要があります。それが JOIN です。
この記事では、2つの表を実際に作って繋ぎ、繋ぎ方を間違えると行が消えること、増えることを、件数を数えて確かめます。あわせて GROUP BY で集計し、WHERE と HAVING の使い分けまで扱います。
対象レベル: 初級〜中級 / 区分: 必修
この記事でわかること
JOINが行を掛け合わせてから絞り込んでいること- 内部結合で行が消えるのはどんなときか。
LEFT JOINとの違い - 気づかないうちに行が増える(1対多の結合)仕組みと、件数での見つけ方
GROUP BYを書いたときに選べる列と選べない列がある理由WHEREとHAVINGの順番の違い
この記事でやること
- 練習用の表を3つ作る(本線で作った表には触りません)
JOINで繋ぎ、件数が合わないことを先に体験するLEFT JOINに変えて、消えていた行を出す- 1対多で行が水増しされるところを、件数で確かめる
GROUP BYで集計し、HAVINGで絞る
先に知る言葉
この記事で初めて出てくる言葉です。 詳しくは用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| 結合(JOIN) | 2 つの表を繋いで 1 つの結果にすること |
| 内部結合 / LEFT JOIN | 両側に相手がいる行だけ返すか、左側を全部残すか |
| 1 対多 | 片方の 1 件に、もう片方が複数ぶら下がる関係。繋ぐと行が増える |
| 集計関数 | SUM や COUNT のように、複数の行を 1 つの値に畳むもの |
| NULL | 値が無いという状態。0 でも空文字でもない |
前提と作業場所
VM の中(Ubuntu Server)で打ちます。 前の回で MySQL を入れて、appdb と
appuser を作ってあることが前提です。
本線で作った tasks / users には一切触りません。 この記事で作る表は
すべて j_ で始めます(join のつもりです)。最後に全部消します。
管理者で入ります。表を作る権限は appuser に渡していないためです。
sudo mysql
USE appdb;
練習用の表を作る
DROP TABLE IF EXISTS j_task;
DROP TABLE IF EXISTS j_member;
DROP TABLE IF EXISTS j_team;
CREATE TABLE j_team (
team_id INT PRIMARY KEY,
team_name VARCHAR(30) NOT NULL
);
CREATE TABLE j_member (
member_id INT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
team_id INT
);
CREATE TABLE j_task (
task_id INT PRIMARY KEY,
title VARCHAR(40) NOT NULL,
member_id INT,
hours INT NOT NULL
);
データを入れます。わざと「所属が無い人」と「誰にも割り当てていないタスク」を混ぜます。
INSERT INTO j_team VALUES (1,'開発'),(2,'運用'),(3,'品質');
INSERT INTO j_member VALUES
(10,'さとう',1),
(11,'たなか',1),
(12,'すずき',2),
(13,'やまだ',NULL);
INSERT INTO j_task VALUES
(100,'画面を作る', 10, 5),
(101,'APIを作る', 10, 8),
(102,'手順書を書く', 11, 2),
(103,'監視を足す', 12, 3),
(104,'棚卸し', NULL, 1);
件数を確かめます。これから何度も数えるので、出発点を覚えておいてください。
SELECT COUNT(*) FROM j_member;
SELECT COUNT(*) FROM j_task;
4
5
JOIN は「掛け合わせてから絞る」
先に、仕組みを1回見ておきます。条件を書かずに繋ぐと、こうなります。
SELECT COUNT(*) FROM j_member, j_team;
12
4 × 3 = 12。 すべての組み合わせが作られました。これが JOIN の出発点です。
ON は、この 12 通りから「正しい組み合わせ」だけを残す条件です。
SELECT COUNT(*)
FROM j_member m
JOIN j_team t ON m.team_id = t.team_id;
3
4 人いたのに 3 行です。 ここが最初の落とし穴で、次で正体を見ます。
内部結合で、行が消える
誰が消えたのかを出します。
SELECT m.name, t.team_name
FROM j_member m
JOIN j_team t ON m.team_id = t.team_id;
+--------+-----------+
| name | team_name |
+--------+-----------+
| さとう | 開発 |
| たなか | 開発 |
| すずき | 運用 |
+--------+-----------+
「やまだ」がいません。 team_id が NULL なので、m.team_id = t.team_id が
どのチームとも成立しなかったためです。
JOIN(内部結合)は、両側に相手がいる行しか返しません。 「人の一覧が欲しい」つもりで書くと、所属していない人が黙って消えます。
これはエラーになりません。 件数が減っているだけなので、気づかずに集計まで進むと、 そのぶん少ない数字が出ます。「人数が合わない」の典型的な原因です。
LEFT JOIN で、消さずに出す
SELECT m.name, t.team_name
FROM j_member m
LEFT JOIN j_team t ON m.team_id = t.team_id;
+--------+-----------+
| name | team_name |
+--------+-----------+
| さとう | 開発 |
| たなか | 開発 |
| すずき | 運用 |
| やまだ | NULL |
+--------+-----------+
4 行に戻りました。 LEFT JOIN は左側(FROM に書いたほう)を必ず全部残し、
相手がいなければ右側の列を NULL で埋めます。
| 書き方 | 返る行 | 使う場面 |
|---|---|---|
JOIN(内部結合) | 両側に相手がいる行だけ | 関係が必ずある前提のとき |
LEFT JOIN | 左側は全部。右は無ければ NULL | 一覧が欲しいとき |
迷ったら「左の表の行数と、結果の行数が同じであるべきか」を考えてください。
同じであるべきなら LEFT JOIN です。
LEFT JOIN を台無しにする書き方
よくある間違いを、わざとやります。チーム名で絞り込みたいときです。
SELECT COUNT(*)
FROM j_member m
LEFT JOIN j_team t ON m.team_id = t.team_id
WHERE t.team_name = '開発';
2
これは意図どおりです。では、「開発チームの人と、所属なしの人」を出したいときは?
SELECT m.name, t.team_name
FROM j_member m
LEFT JOIN j_team t ON m.team_id = t.team_id AND t.team_name = '開発';
+--------+-----------+
| さとう | 開発 |
| たなか | 開発 |
| すずき | NULL |
| やまだ | NULL |
+--------+-----------+
条件を ON に書くか WHERE に書くかで、結果がまったく変わります。
| どこに書くか | 何が起きるか |
|---|---|
ON に書く | 繋ぐときの条件。 合わなくても左側の行は残る |
WHERE に書く | 繋いだ後の絞り込み。 NULL の行は落ちる |
LEFT JOIN したのに WHERE で右側の列を条件にすると、内部結合と同じになります。
「LEFT JOIN にしたのに行が減る」の原因は、ほぼこれです。
気づかないうちに、行が増える
今度は逆です。1人が複数のタスクを持っているので、繋ぐと増えます。
SELECT COUNT(*) FROM j_member;
SELECT COUNT(*)
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id;
4
5
4 人が 5 行になりました。 「さとう」がタスクを2つ持っているためです。中身を見ます。
SELECT m.name, k.title
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id;
+--------+------------+
| さとう | 画面を作る |
| さとう | APIを作る |
| たなか | 手順書を書く |
| すずき | 監視を足す |
| やまだ | NULL |
+--------+------------+
これ自体は正しい結果です。 危ないのは、この状態で人の側の数字を集計したときです。
1対多を繋いだ結果は、「人の一覧」ではなく「人とタスクの組の一覧」です。 そのまま人数を数えると、多いほうの件数になります。
SELECT COUNT(*), COUNT(DISTINCT m.member_id)
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id;
5 4
COUNT(*) は 5、実際の人数は 4。 DISTINCT を付けると正しく数えられます。
結合したあとの COUNT(*) は、たいてい意味を持ちません。
GROUP BY で集計する
人ごとの合計時間を出します。
SELECT m.name, SUM(k.hours) AS total
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id
GROUP BY m.name;
+--------+-------+
| name | total |
+--------+-------+
| さとう | 13 |
| たなか | 2 |
| すずき | 3 |
| やまだ | NULL |
+--------+-------+
GROUP BY は「同じ値の行を1行にまとめる」命令です。まとめた結果、
1行に複数の値が入ってしまう列は、そのままでは選べません。
試します。
SELECT m.name, k.title, SUM(k.hours)
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id
GROUP BY m.name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'appdb.k.title' which is not functionally
dependent on columns in GROUP BY clause
「さとう」は 1 行にまとまったのに、title は2つある。 どちらを出せばいいか
決められないので、拒否されました。これは親切なエラーです。
選べるのは2種類だけです。
| 選べる列 | 理由 |
|---|---|
GROUP BY に書いた列 | まとめる基準なので、1グループに1つしかない |
SUM() / COUNT() などでまとめた結果 | 複数を1つの値に畳んでいる |
古い MySQL では、この状態でもエラーにならず「どれか1つ」を勝手に返していました。 値が日によって変わる不具合の原因になるため、いまは既定で拒否されます。
WHERE と HAVING
「合計3時間より多い人」だけを出します。WHERE では書けません。
SELECT m.name, SUM(k.hours) AS total
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id
WHERE SUM(k.hours) > 3
GROUP BY m.name;
ERROR 1111 (HY000): Invalid use of group function
WHERE はまとめる前に働くので、合計はまだ存在しません。
まとめた後に絞るのが HAVING です。
SELECT m.name, SUM(k.hours) AS total
FROM j_member m
LEFT JOIN j_task k ON m.member_id = k.member_id
GROUP BY m.name
HAVING total > 3;
+--------+-------+
| さとう | 13 |
+--------+-------+
順番は「繋ぐ →
WHEREで絞る → まとめる →HAVINGで絞る」。 この順番が、そのまま書ける場所を決めています。
どちらでも書ける条件は WHERE に書いてください。 まとめる前に減らしたほうが、
処理する行が少なくて済みます。
つまずいたところ
人数を数えたら、実際より多く出ました。 タスクと結合したあとの COUNT(*) を
そのまま使っていたためです。結合したら、数える前に「何の一覧になっているか」を言葉にする
ようにしてから、この間違いは無くなりました。
LEFT JOIN にしたのに行が減りました。 右側の列を WHERE で絞っていたためです。
LEFT JOIN の相手側に条件を付けるときは ON に書くと覚え直しました。
GROUP BY のエラーを、設定を変えて黙らせようとしました。 通ることは通りますが、
返る値が実行のたびに変わりうると知って戻しました。エラーのほうが安全です。
後片づけ
この記事で作った表を消します。j_ で始まる3つだけです。
DROP TABLE IF EXISTS j_task;
DROP TABLE IF EXISTS j_member;
DROP TABLE IF EXISTS j_team;
SHOW TABLES;
本線で作った tasks / users が残っていることを確認してください。
消す順番は、参照している側から先です(ここでは外部キーを張っていないので順不同です)。
EXIT;
まとめ
JOINは全部の組み合わせを作ってからONで絞る。だから増えることも減ることもある- 内部結合は、相手がいない行を黙って落とす。 一覧が欲しいなら
LEFT JOIN LEFT JOINの相手側の条件はONに書く。WHEREに書くと内部結合と同じになる- 1対多を繋いだあとの
COUNT(*)は組の数。人数ならCOUNT(DISTINCT ...) - まとめる前は
WHERE、まとめた後はHAVING。どちらでも書けるならWHERE
参考リンク
次に読む
この記事は 【第5段階】バックエンドとデータベース の 4 / 29 本目です。
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。

コメント