あるクエリを別のクエリの中に入れ子にして、集計値と比較したり、集合で絞り込んだり、存在をテストしたりしましょう。
このレッスンを Kodokon で開くサブクエリとは、別のクエリの中の丸括弧に置かれたクエリのことです。一つの文で「平均より上の注文」や「少なくとも一つ注文のある顧客」といった問いに答えます。始める前に、このスクリプトを sqliteonline.com か sqlite3 で実行しましょう。
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount REAL NOT NULL
);
INSERT INTO customers (id, name) VALUES
(1, 'Alice'), (2, 'Karim'),
(3, 'Mina'), (4, 'Paulo');
INSERT INTO orders (id, customer_id, amount)
VALUES
(1, 1, 49.90), (2, 1, 15.00),
(3, 2, 120.50), (4, 2, 80.00),
(5, 3, 9.90), (6, 1, 200.00);WHERE に置かれたスカラーサブクエリは、単一の値、つまり一行一列を返さなければなりません。エンジンはまずそれを計算し、それから他の定数と同じように使います。単純な WHERE amount > AVG(amount) ではこれはできません。SQLでは禁じられているのです。
SELECT id, customer_id, amount
FROM orders
WHERE amount > (
SELECT AVG(amount) FROM orders
);IN は、列を、サブクエリが返す値の集合と比較します。EXISTS は、相関サブクエリ（外側のクエリの列である c.id を参照します）が少なくとも一行を返すかどうかをテストします。エンジンは最初の一致が見つかったらすぐに止まります。
SELECT name FROM customers
WHERE id IN (
SELECT customer_id FROM orders
WHERE amount > 100
);
SELECT c.name FROM customers AS c
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.id
);FROM の中では、サブクエリは一時的なテーブルのように振る舞い、導出テーブルと呼ばれ、エイリアスを付けなければなりません。二段階で集計するための定番の道具です。まず顧客ごとの合計、次にそれらの合計の平均です。
SELECT ROUND(AVG(t.total), 2) AS avg_basket
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) AS t;