女性向けエンジニアスクール
3秒でわかる
SQL文の中に入れ子で書く別のSELECT文。平均より高い商品を出すなど、集計した結果を条件に使いたいときに欠かせない書き方です。
もう少し詳しく
どういうものか
サブクエリは、SQL文の中にかっこで囲んで書く別の SELECT 文です。内側が先に実行され、その結果を外側が使います。書ける場所は主に3つあり、WHERE の条件の中、FROM の中(派生テーブル)、そして SELECT の列の位置です。
返る形によって使える演算子が変わります。1行1列なら = や > で直接比べられ、複数行1列なら IN や EXISTS を使います。
なぜ必要か
集計した結果を条件に使いたいとき、1回の SELECT では書けないためです。「平均より高い価格の商品」を出そうとして WHERE price > AVG(price) と書くとエラーになります。WHERE は1行ずつ評価される段階で動くので、全体を見渡す集計関数をそこに置けません。平均を先に別の問い合わせで求めて、その値を条件に渡す必要があります。
具体例
-- 平均価格より高い商品を出す
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- 一度でも注文された商品だけを出す
SELECT name
FROM products p
WHERE EXISTS (
SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);2つ目のように外側の列を内側から参照するものを相関サブクエリと呼びます。外側の1行ごとに内側が評価されるため、行数が多いと遅くなりやすい形です。
つまずきやすいところ
NOT IN と NULL の組み合わせが典型的な落とし穴です。内側の結果に NULL がひとつでも混ざると、NOT IN は一致するものが無いと判断され、結果が常に空になります。
-- customer_id に NULL があると 0 件になる
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- NOT EXISTS なら NULL があっても意図どおり動く
SELECT * FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);もうひとつ、FROM に書いたサブクエリには別名が必須のデータベースがあります。MySQL では別名を付けないと構文エラーになるので、) AS t のように名前を付けます。
似た用語との違い
サブクエリと JOIN は、どちらでも書ける場面が多くあります。複数のテーブルから列を並べて表示したいなら JOIN、存在するかどうかだけを条件にしたいならサブクエリ、と考えると選びやすくなります。JOIN で書くと1件のはずが重複して増えることがありますが、EXISTS は行を増やしません。
WITH 句(共通テーブル式)は、FROM のサブクエリに名前を付けて先頭に出したものです。入れ子が2段以上になったら WITH に書き換えると、上から順に読める形になります。
