SQL Lab — MySQL を大容量で叩いて学ぶ

前提: 第 5 章のあと npm run seed:basics で作り直してある。

この章で答える問い

SELECT の結果を、別の SELECT の中で使うには
IN と EXISTS はどう違うか
NOT IN で行が全部消えるのはなぜか

実物: SELECT の結果は 3 通りの形で使える

括弧で囲んだ SELECT は、その結果の形によって、値・集合・表のどれかとして扱える。3 つを順に見る。

値として: 平均より高い明細

期待: 行数を見る。括弧の中が先に 1 つの数(平均 636.3)になる

⌘/Ctrl + Enter で実行

SELECT id, product_id, unit_price
FROM order_items
WHERE unit_price > (SELECT AVG(unit_price) FROM order_items)
ORDER BY unit_price DESC

(SELECT AVG(unit_price) FROM order_items) は 1 行 1 列を返すので、1 つの値として > の右に置ける。これをサブクエリと呼ぶ。「クエリの中のクエリ」だ。

集合として: gold 会員の注文

期待: 22 行

⌘/Ctrl + Enter で実行

SELECT id, customer_id, ordered_at
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'gold')

(SELECT id FROM customers WHERE tier = 'gold') は id の集合(2 つの値)を返すので、IN の右に置ける。第 4 章の JOIN でも同じ結果が出せる。顧客の列(名前など)を出すなら JOIN、顧客は絞り込みにだけ使って出さないならサブクエリ、と使い分けると意図が読みやすい。

表として: 顧客あたり平均注文数

期待: 1 行。平均 6.25、最大 12

⌘/Ctrl + Enter で実行

SELECT AVG(t.件数) AS 平均, MAX(t.件数) AS 最大
FROM (
  SELECT customer_id, COUNT(*) AS 件数
  FROM orders
  GROUP BY customer_id
) AS t

FROM の中の SELECT は表として扱われ、別名(t)が必須になる。「集計した結果を、さらに集計する」ときの形だ。派生テーブルと呼ぶ。

EXISTS: 対応する行が 1 つでもあるか

キャンセルしたことのある顧客

期待: 2 行

⌘/Ctrl + Enter で実行

SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'cancelled'
)

括弧の中に c.id という外側の列が出てくる。「顧客 1 行ごとに、その顧客のキャンセル注文があるかを調べる」と読む。外側の行を参照するサブクエリを相関サブクエリと呼ぶ。SELECT 1 は「何を返すかはどうでもよく、あるかないかだけ」の意味。

一度もキャンセルしていない顧客(チケット完了)

期待: 8 行

⌘/Ctrl + Enter で実行

SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'cancelled'
)

驚き: NOT IN が全部消す

「発送日時と同じ時刻に注文された注文はないはず」を確かめるため、注文日時が発送日時の集合に含まれない注文を数える。50 行返るはずだ。予測してから実行する。

NOT IN と NULL

期待: 予測してから実行

⌘/Ctrl + Enter で実行

実行する前に予測する返る行数

SELECT COUNT(*) FROM orders
WHERE ordered_at NOT IN (SELECT shipped_at FROM orders)

0 のはずだ。50 ではない。shipped_at の集合に NULL が 3 つ入っている。x NOT IN (a, b, NULL) は x <> a AND x <> b AND x <> NULL で(これは意味の展開で、SQL にそう書くわけではない)、最後が「不明」なので全体が不明になり、全行が落ちる(第 2 章の 3 値論理が、集合の中に潜んで出てきた)。集合の要素ごとの比較と AND のまとめ方を、注文 2 で描く。

NOT IN は集合の各要素と <> で比べて AND でまとめる。集合に NULL が 1 つでもあると全体が「不明」になり、その行は落ちる。

NULL を除く

期待: 50

⌘/Ctrl + Enter で実行

SELECT COUNT(*) FROM orders
WHERE ordered_at NOT IN (SELECT shipped_at FROM orders WHERE shipped_at IS NOT NULL)

NOT IN の中は NULL を除く。あるいは NOT EXISTS で書く。NOT EXISTS は「あるかないか」しか見ないので NULL の罠がない。 NULL を許した列に対しては NOT EXISTS を選ぶ人が多い。

WITH: サブクエリに名前を付ける

派生テーブルが入れ子になると読めない。WITH 名前 AS (…) で先に定義してから使う。

平均より多く注文している顧客

期待: 上から順に読める

⌘/Ctrl + Enter で実行

WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS 件数
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, p.件数
FROM per_customer p
JOIN customers c ON c.id = p.customer_id
WHERE p.件数 > (SELECT AVG(件数) FROM per_customer)
ORDER BY p.件数 DESC

per_customer を 2 回使っている(JOIN と、平均の計算)。派生テーブルなら同じ SELECT を 2 回書く必要があった。複数の WITH はカンマで並べ、後のものから前のものを参照できる。

2 段の WITH: 月ごとの売上と、平均との差

期待: 3 行

⌘/Ctrl + Enter で実行

WITH monthly AS (
  SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS 月, SUM(oi.quantity * oi.unit_price) AS 売上
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY 月
),
with_avg AS (
  SELECT 月, 売上, 売上 - (SELECT AVG(売上) FROM monthly) AS 平均との差
  FROM monthly
)
SELECT * FROM with_avg ORDER BY 月

括弧で囲んだ SELECT は、結果の形によって値・集合・表として使える。NOT IN の集合に NULL があると全行が落ちる。

確認

括弧で囲んだ SELECT が、値・集合・表のどれとして扱われるかは何で決まるか。

中の SELECT が返す結果の形で決まる。1 行 1 列なら値として比較の右に置け、1 列で複数行なら集合として IN の右に置け、FROM の中に書けば表(派生テーブル)になり、そのときは別名が要る。

NULL を許した列に対して、NOT IN より NOT EXISTS が好まれるのはなぜか。

NOT IN は集合の要素ごとの比較を AND でまとめるので、集合に NULL が 1 つでもあると全体が「不明」になり、その行が落ちる。NOT EXISTS は対応する行があるかないかしか見ないため、この罠がない。

同じ集計を 2 か所で使うとき、派生テーブルより WITH が読みやすいのはなぜか。

WITH は先に名前を付けて定義するので、同じ SELECT を 2 回書かずに済み、上から順に読める。派生テーブルは入れ子が深くなるほど読めなくなる。

次の章へ

次の「補足: 行を潰さずに集計する」は、飛ばしても第二部に影響しない。第 7 章では、注文を入れて在庫を減らす 2 つの文を「1 つの操作」にする方法を、 2 本の接続を交互に操作しながら体験する。