相関サブクエリと EXISTS ― 行ごとに問い合わせる

導入

第3章でサブクエリ（クエリの中のクエリ）を学びました。あそこでのサブクエリは「先に1回だけ実行して、その結果を使う」ものでした。今回は一歩進んで、外側の行1つ1つに連動して動くサブクエリ――相関サブクエリを扱います。「各会員について、その人の注文数を数える」のように、行ごとの問い合わせができます。

説明

相関サブクエリは、内側の WHERE で外側の列を参照します。下の例では、内側の o.user_id = u.id の u.id が外側 users u の各行を指しています。外側の1行ごとに、内側が「その人の注文数」を計算します。

SELECT name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

flowchart LR
    U["users の各行<br/>（u.id が変わる）"] -->|"1行ごとに"| S["内側: orders を<br/>o.user_id = u.id で数える"]
    S --> R["その人の注文数"]

EXISTS ― 「あるかないか」だけを見る。件数まではいらず「1件でも該当があるか」を判定したいときは EXISTS が速くて明快です。「注文したことがある会員」はこう書けます。

SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

逆に「一度も注文していない会員」は NOT EXISTS。前レッスンの EXCEPT と同じ答え（山本さん）になりますが、EXISTS は「条件を満たす行があるか」を直接表現できるのが強みです。IN と似ていますが、NULL がからむ場面や大きなテーブルでは EXISTS のほうが素直に働きます。

やってみよう

初期表示のクエリを実行し、各会員の横に注文数が並ぶことを確認しましょう（山本さんは 0 のはずです）。次に WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id) を users u に付けて、未注文の会員だけを絞り込んでみてください。

演習

EXISTS を使って、一度でも注文された商品の name を products p から取り出してください（order_items にその商品が登場するかを EXISTS で判定します）。