値をひとつ返すサブクエリ
値ひとつを書ける場所には、1行1列だけを返すクエリを代わりに書けます。
1
SELECT name,
price,
ROUND((SELECT AVG(price) FROM products), 2) AS average_price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC
LIMIT 20;結果 — 20 行のうち先頭 5 行
| name | price | average_price |
|---|---|---|
| Glide GPS Watch Pro | 2484.4 | 1230.21 |
| Terra 3D Puzzle | 2481.77 | 1230.21 |
| Aria Castle Kit | 2479.41 | 1230.21 |
| Aria LED Strip Pro | 2463.41 | 1230.21 |
| Nova Brief Answers | 2445.49 | 1230.21 |
ブラウザの別タブで Planche が開き、サンプルデータベースを読み込んだ状態でこのクエリが入ります。このページの例はどれも同じタブを使い回します。F9 を押すまで実行はされません。
(SELECT AVG(price) FROM products) は数字をひとつ作り、残りの部分はそれを、あなたがその数字を打ち込んだかのように扱います。これが「なぜ WHERE price > AVG(price) と書けないのか」への答えでもあります。集計はグループを要約するものですが、WHERE が動いている時点ではまだグループがありません。サブクエリはそれ自体が完結したクエリで、外側が絞り込まれる前に終わっているので、この順序をすり抜けられます。
リストを返すサブクエリ
2
SELECT first_name, last_name, email, country
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
WHERE status = 'refunded'
)
ORDER BY last_name
LIMIT 20;結果 — 20 行のうち先頭 5 行
| first_name | last_name | country | |
|---|---|---|---|
| Maya | Ahmed | maya.ahmed1@mail.example | US |
| Noah | Ahmed | noah.ahmed@inbox.example | FR |
| Liam | Ahmed | liam.ahmed1@mail.example | DE |
| Aisha | Ahmed | aisha.ahmed@mail.example | GB |
| Sofia | Ahmed | sofia.ahmed@example.com | ES |
内側のクエリが顧客IDの列を作り、IN がそこに含まれるかを見ます。内側から外へ読んでください。「返金された注文の顧客IDの中に、自分のIDがある顧客」です。
EXISTS：一致したかどうかだけを聞く
3
SELECT c.first_name, c.last_name, c.country
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.total > 1000
)
ORDER BY c.last_name
LIMIT 20;結果 — 20 行のうち先頭 5 行
| first_name | last_name | country |
|---|---|---|
| Aisha | Ahmed | GB |
| Aisha | Ahmed | JP |
| Ava | Ahmed | IT |
| Emma | Ahmed | IN |
| Ethan | Ahmed | FR |
EXISTS はクエリを取り、それが1行でも返せば真になります。SELECT 1 は慣習です。選んだ値は誰も見ず、行が返ったかどうかしか見ないので、本物の列を選ぶ意味がありません。
このサブクエリは外側の c.id を参照しています。これが相関サブクエリで、一度計算するリストではなく、顧客ごとに違う質問になります。「関連する行があるか」を聞くなら、たいていこれがいちばん読みやすく、エンジンもよく最適化してくれる形です。
NOT IN の罠
120人の従業員のうち110人には部下がいません。それを探すクエリです。
4
-- Employees nobody reports to. 110 of the 120 qualify,
-- and this query finds none of them.
SELECT first_name, last_name, position
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);該当する行なし
0行。エラーもなし。
manager_id は NULL を許す列で、組織図の頂点にいる10人はそこが NULL です。だから内側のクエリは NULL を含むリストを返し、x NOT IN (1, 2, NULL) は「x は1と違い、2とも違い、この不明な値とも違うか」と聞くことになります。最後の部分が不明なので条件全体が不明になり、どの行も残りません。どんな x であってもです。リストのどこかに NULL がひとつあるだけで、結果は空になります。
IN は同じようには壊れません。実在の値に一致した行は、リストに他に何が入っていても残ります。すべての比較が真でなければならないのは否定形だけで、だから不明がひとつあれば空になるのです。
直し方は、サブクエリの中に WHERE manager_id IS NOT NULL を足すか、あるいは忘れようがないぶん優れている NOT EXISTS に替えるかです。
5
SELECT e.first_name, e.last_name, e.position
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM employees m
WHERE m.manager_id = e.id
)
ORDER BY e.last_name
LIMIT 20;結果 — 20 行のうち先頭 5 行
| first_name | last_name | position |
|---|---|---|
| Priya | Ahmed | Senior Engineer |
| Diego | Ahmed | Intern |
| Yuki | Ahmed | Engineer |
| Ravi | Brown | Lead |
| Jonas | Brown | Specialist |
NOT EXISTS は値ではなく行の有無を聞くので、NULL が比較に入ってきません。既定で NOT EXISTS に手を伸ばすようにしておくと、黙って間違う類の答えを丸ごと1種類なくせます。
テーブルとしてのサブクエリ
複数行・複数列を返すクエリは、テーブルが書ける場所、つまり FROM に置けます。そういうクエリを導出テーブルと呼びます。
6
SELECT category, products, average
FROM (
SELECT cat.name AS category,
COUNT(*) AS products,
ROUND(AVG(p.price), 2) AS average
FROM products p
JOIN categories cat ON cat.id = p.category_id
GROUP BY cat.id
)
WHERE products >= 10
ORDER BY average DESC;結果 — 26 行のうち先頭 5 行
| category | products | average |
|---|---|---|
| Wearables | 11 | 1672.29 |
| Programming | 10 | 1585.67 |
| Tablets | 17 | 1529.45 |
| Audio | 11 | 1482.89 |
| Kitchen | 12 | 1443.21 |
これが「集計で絞り込みたい」へのもうひとつの答えです。内側でグループ化し、その結果を普通のテーブルのように絞り込みます。HAVING と同じ仕事をしますが、HAVING が移植性を保ったままではできない別名での絞り込みもできます。
導出テーブルは入れ子にできて、入れ子になった途端に追いにくくなります。3段も重なれば内側から読むしかありません。次のレッスンでは、それを上から順に読める形に置き換えます。
行ごとに1本のサブクエリ
ここまでで書けるもの
複雑なものを読みやすくすること以外は、ひととおり書けます。それが次のレッスンです。