SQLite サブクエリ入門：WHERE/FROM/SELECT で SELECT をネスト

サブクエリとは「SELECT の中の SELECT」

SQLite のサブクエリは、その名のとおり SELECT 文の中にもう一つ SELECT 文を入れ子にしたもので、内側のクエリを丸かっこで囲んで書きます。SQLite はまず内側のクエリを実行し、その結果を外側のクエリに渡して処理を続けます。

これから繰り返し使えるよう、簡単なサンプルデータを用意しておきましょう。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders;

注文5件、顧客4人、そのうち2人はまだ何も注文していない――このデータをこの先ずっと使い回していきます。

WHERE 句で使う sqlite サブクエリ：リストで絞り込む

一番よく見るパターンがこれ。内側のクエリで id のリストを取ってきて、外側のクエリでそれを使ってフィルタする、という形です。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

内側のクエリは、orders に登場するすべての customer_id を返します。外側のクエリは、その一覧に id が含まれる顧客だけを残す、という流れです。結果として Cleo、Boris、Ada が出てきますが、注文のない Dmitri は弾かれます。

IN (SELECT ...) は「A のうち B にマッチがある行」を抽出する定番パターンです。頭の中では「この列の値が、内側のクエリが返す値のいずれかと一致する場合」と読み下すとスッと入ってきます。

NOT IN を使うときは NULL に要注意

逆に「どの顧客が まだ 注文していないか?」を知りたい場合は、ほんの一行書き換えるだけで済みます。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

ここまではうまくいきます。ただし NOT IN には落とし穴があって、サブクエリが一度でも NULL を返すと、NOT IN 全体が NULL(つまり TRUE ではない)になり、結果は 0 件 になってしまいます。エラーも出ないので、気づきにくいんですよね。

NULL が混じる可能性のあるカラムに対して NOT IN を使うときの安全な書き方はこちらです:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

あるいは、こうした問題が一切起きない NOT EXISTS を使う手もあります。これについては後ほど取り上げます。

スカラーサブクエリ:1行1列だけ返すサブクエリ

スカラーサブクエリは、1行・1列という単一の値を返すサブクエリで、値が必要な場所ならどこでも使えます。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name, total
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE total = (SELECT MAX(total) FROM orders);

内側の SELECT MAX(total) FROM orders が 200 を返し、外側のクエリでその値に一致する注文だけを抽出します。集計値と比較したい場面で何かと重宝するパターンです。

スカラーサブクエリは SELECT 句の中に書くこともできて、各行に計算結果をくっつけたいときに便利です。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers;

customers の行ごとに、customers.id を差し込んで内側のクエリが1回ずつ実行されます。これがいわゆる 相関サブクエリ(correlated subquery)で、詳しくは後ほど解説します。今回のように「1行につき1つの値が欲しい」というケースでは、LEFT JOIN と GROUP BY の組み合わせのほうがパフォーマンス的に有利なことが多いですが、スカラーサブクエリの書き方は何より読みやすいのが魅力です。

EXISTS の使い方:行が存在するかだけを確認する

EXISTS は IN の地味な親戚のような存在です。値そのものには関心がなく、サブクエリが何らかの行を返すかどうかだけをチェックします。中身のカラムは何でもよいので、慣例的に SELECT 1 と書くことが多いですね。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.total > 100
);

これは「100を超える注文を1件でも出している顧客」を取り出すクエリです。内側のクエリで外側の c.id を参照しているので、いわゆる相関サブクエリになります。SQLite はマッチが1件見つかった時点で内側のスキャンを打ち切るため、「関連する行があるか?」を判定したいケースでは EXISTS が IN より速くなることが多いです。

否定形の NOT EXISTS は、「関連する行が存在しない」を NULL セーフに表現できる書き方です:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

FROM句のサブクエリ：派生テーブルとして使う

SQLiteのサブクエリは、テーブルが書ける場所ならどこにでも置けます。FROM句もその一つです。内側のクエリに別名を付ければ、一時的な「派生テーブル（derived table）」として扱えるので、それに対してJOINしたり、絞り込んだり、集計したりできます。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT country, AVG(customer_total) AS avg_per_customer
FROM (
    SELECT c.country, c.id, SUM(o.total) AS customer_total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
) AS per_customer
GROUP BY country;

内側のクエリで顧客ごとの合計を出し、外側のクエリでその合計を国ごとに平均しています。こういう2段構えの集計こそ、派生テーブル(derived table)の出番です。1回の GROUP BY で片付けられないときに使う、まさに定番のパターンですね。

なお、AS per_customer のエイリアスは省略できません。派生テーブルには必ず名前を付ける必要があります。

相関サブクエリ:外側の行ごとに実行される

外側のクエリの列を参照しているサブクエリのことを「相関サブクエリ(correlated subquery)」と呼びます。SQLite は外側の行が1行進むたびに内側のクエリを評価し直すので、柔軟に書ける反面、コストがかさみやすいので注意が必要です。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name,
       (SELECT MAX(total)
        FROM orders
        WHERE customer_id = customers.id) AS biggest_order
FROM customers;

各顧客について、それぞれの最大注文額を取得します。内側のクエリは customers.id に依存しているため、顧客 1 人につき 1 回ずつ実行されます。注文がない顧客は NULL になりますが、たいていはこれが望ましい挙動です。

「テーブル A の各行に対して、テーブル B から何かを計算する」という用途には、相関サブクエリがぴったりハマります。テーブルが小さい場合や、検索対象にインデックスが張られている場合は問題ありません。ただし、インデックスのない大きなテーブルではリリース前にプロファイリングしておきましょう。多くのケースでは JOIN と GROUP BY の組み合わせの方が速くなります。

sqlite サブクエリと JOIN の使い分け

次の 2 つのクエリは、同じ問いに対する答えを返します。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- サブクエリ
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- JOIN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

どちらを書いても返ってくる行は同じです。SQLite のオプティマイザは内部で片方をもう片方に書き換えることもよくあります。なので選ぶ基準は 読みやすさ です。

• 単に絞り込みたいだけで、内側のテーブルのカラムを結果に混ぜたくないときは サブクエリ を使う。
• 両方のテーブルからカラムが欲しいときは JOIN を使う。
• 「関連する行が1件でも存在するか?」を聞きたいときは EXISTS を使う。意図が明確で、IN / NOT IN にありがちな NULL のワナも避けられます。

迷ったら、声に出して読んだときに意味がそのまま伝わる方を選びましょう。

よくある落とし穴: 複数行を返すサブクエリ

= と一緒に使うサブクエリは、返す行が最大でも1行でなければなりません。複数行返ってしまうと、SQLite は(実質的にランダムに)そのうちの1つを採用し、エラーも出さずに静かに間違った結果を返します。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- 危険: UK出身の顧客が複数いたらどうなる?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');

内側のクエリが複数行を返す可能性がある場合は、IN を使います:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');

もし「結果は1行のはず」と前提を置いて、それをきちんと担保したいなら、LIMIT 1 と ORDER BY を組み合わせて、せめて取得される行が決定的になるようにしておきましょう。さらに望ましいのは、データ側で1行に絞られることが保証されるようにクエリを書くこと（ユニークなカラムで絞り込む、など）です。

次回：共通テーブル式（CTE）

FROM 句のサブクエリは、すぐに見通しが悪くなります。同じ派生テーブルを2回使いたい場合や、ネストが3階層にもなる場合は特にそうです。共通テーブル式（WITH ... AS (...)）を使えば、サブクエリにあらかじめ名前を付けておき、以降のステートメントからその名前で参照できます。次のページで詳しく見ていきましょう。