会員を結合とサブクエリで見る

01 / 05

会員の姿は2つの表にまたがる

「よく買っているのはだれ?」に答えるには、members（だれか）と orders（何を買ったか）の両方がいる。

片方だけ見ても人物像は出てこない。番号でつないでからまとめるのがこの節の型だよ。

02 / 05

結合してからまとめる

結合してできた表は、ふつうの表と同じようにまとめられる。GROUP BY m.id なら会員ごと、GROUP BY m.area なら地域ごとだね。

名前でまとめず id でまとめるのは、同じ名前の会員がいても混ざらないようにするためだよ。

SELECT m.name, COUNT(*) AS 件数  FROM members m  JOIN orders o    ON m.id = o.member_id  GROUP BY m.id  ORDER BY m.id LIMIT 3;

実行結果

あおい | 4
はると | 1
みなと | 1

03 / 05

結合すると消える行がある

JOIN は両方にある行だけを残す。だから一度も買っていない会員は、結合した時点で結果から消えてしまうんだ。

「まだ買っていない人」を知りたいときは、買った人の番号をサブクエリで作って、そこに入っていない人を探すよ。

SELECT id, name  FROM members  WHERE id NOT IN (    SELECT member_id FROM orders  )  ORDER BY id;

実行結果

5 | そら
10 | ふうか

04 / 05

基準の値もサブクエリで作る

「平均より年上の会員」のように、先に基準の値を1つ作ってから比べるときもサブクエリの出番。

かっこの中は先に計算されて、ただの数値になる。だから > の右側にそのまま置けるんだ。

SELECT name, age  FROM members  WHERE age > (    SELECT AVG(age) FROM members  )  ORDER BY age DESC;

実行結果

つむぎ | 52
なぎ | 45
りく | 41
ふうか | 38
しおん | 36
ひなた | 34

05 / 05

3つの表をつなぐ

金額まで出すには members orders items の3つがいる。JOIN は続けて何回でも書けるよ。

つなぐ順番は結果を変えない。どの列とどの列が対応するかさえ間違えなければ大丈夫。ここから書いてみよう。

SELECT m.name,       SUM(o.quantity * i.price)         AS 金額  FROM members m  JOIN orders o    ON m.id = o.member_id  JOIN items i    ON o.item_id = i.id  GROUP BY m.id  ORDER BY m.id LIMIT 3;

実行結果

あおい | 5900
はると | 2000
みなと | 2000