
SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴
当サイトは Amazon.co.jp を宣伝しリンクすることで紹介料を得る手段を提供する、Amazonアソシエイト・プログラムの参加者です。価格・在庫はリンク先の最新情報をご確認ください。
「平均より高い注文だけを出したい」「注文が1件もない顧客を探したい」「顧客ごとに最新の注文を1件だけ並べたい」。どれも、クエリの中に別のクエリを書くサブクエリ(副問い合わせ)で書ける処理です。一方で、NOT IN で書いたら結果が空になった、JOIN に書き換えたら行が増えた、SELECT に書いたサブクエリが遅い、といったつまずきも多い機能です。
サブクエリは、返す結果の形(1つの値か、1列の一覧か、表か)と、外側のクエリの列を参照するかどうか(相関するか)で性質が決まります。この2つを押さえると、どこに書けるか、どの演算子と組み合わせるか、なぜ遅くなるのかが整理しやすくなります。
この記事では、小さなサンプルテーブルを最初に1回だけ定義し、すべてのクエリをそのデータに対して実行して結果を並べます。スカラーサブクエリから始めて、IN・EXISTS、NOT IN と NULL の罠、相関サブクエリ、派生テーブル、ANY・ALL、LATERAL を順に確認し、最後に JOIN や CTE との使い分けと、データベースがサブクエリをどう実行するかを見ます。
NOTE
記事中の結果表は、特記のない限り SQLite 3.54.0 と PostgreSQL 18.6 の両方で実行し、同じ結果になることを確かめたものです(AVG の小数点以下の表示桁数だけは製品で異なり、表には SQLite の出力を載せています)。結果表の NULL は NULL 値を表します。ANY・ALL・LATERAL の結果は PostgreSQL 18.6 だけで実行しました(SQLite 3.54.0 は構文エラーになります)。MySQL の挙動は MySQL 8.4 リファレンスマニュアルで確認した内容を載せています(手元での実行はしていません)。
先に結論 - 早見表
| やりたいこと | 書き方 | 注意点 |
|---|---|---|
| 1つの値と比べる・列に並べる | スカラーサブクエリ (SELECT AVG(x) FROM t) | 0 行なら NULL。2 行以上は PostgreSQL・MySQL でエラー、SQLite は先頭行を使う |
| 一覧に含まれる行を残す | IN (SELECT ...) | JOIN と違って外側の行が重複しない |
| 関連する行が1件でもある行を残す | EXISTS (SELECT 1 ...) | SELECT リストの中身は結果に影響しない |
| 関連する行が1件もない行を残す | NOT EXISTS (SELECT 1 ...) | NOT IN は一覧に NULL があると 0 行になる |
| 外側の行ごとに条件を変える | 相関サブクエリ | 書き方によっては外側の行の数だけ実行される |
| 集計した結果をさらに集計・結合する | FROM (SELECT ...) AS t(派生テーブル) | MySQL は別名が必須。PostgreSQL 16 以降と SQLite は省略可 |
| 一覧のどれか・すべてと比べる | > ANY (...) / > ALL (...) | ALL は一覧が空だと真。SQLite は非対応 |
| 外側の行ごとに複数列・複数行を取る | LEFT JOIN LATERAL (...) ON true | PostgreSQL・MySQL。SQLite は非対応でウィンドウ関数で代用 |
| 同じ途中結果を何度も使う・段階を読みやすくする | CTE(WITH) | 派生テーブルに名前を付けて上に出したもの |
サンプルデータ
以降の例はすべて次のデータを使います。サンプル商店(仮)の顧客4人と、注文5件です。
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id), -- ゲスト購入は NULL
amount INTEGER NOT NULL,
ordered_on DATE NOT NULL
);
INSERT INTO customers VALUES
(1, '顧客A', '東京'), (2, '顧客B', '大阪'), (3, '顧客C', '東京'), (4, '顧客D', '名古屋');
INSERT INTO orders VALUES
(101, 1, 1200, '2026-11-01'),
(102, 1, 3000, '2026-11-05'),
(103, 2, 800, '2026-11-03'),
(104, 2, 2500, '2026-11-10'),
(105, NULL, 1500, '2026-11-08');customers テーブル:
| id | name | city |
|---|---|---|
| 1 | 顧客A | 東京 |
| 2 | 顧客B | 大阪 |
| 3 | 顧客C | 東京 |
| 4 | 顧客D | 名古屋 |
orders テーブル:
| id | customer_id | amount | ordered_on |
|---|---|---|---|
| 101 | 1 | 1200 | 2026-11-01 |
| 102 | 1 | 3000 | 2026-11-05 |
| 103 | 2 | 800 | 2026-11-03 |
| 104 | 2 | 2500 | 2026-11-10 |
| 105 | NULL | 1500 | 2026-11-08 |
ポイントは次の3つです。
- 顧客A と顧客B には注文が2件ずつある
- 顧客C と顧客D には注文が1件もない
- 注文 105 はゲスト購入で、
customer_idが NULL
サブクエリとは - クエリの中に書くクエリ
サブクエリは、かっこで囲んで別のクエリの中に書いた SELECT 文です。外側のクエリから見ると、サブクエリは「値」か「一覧」か「表」として扱われます。
| 種類 | 返すもの | 主な書き場所 | 組み合わせる演算子 |
|---|---|---|---|
| スカラーサブクエリ | 1行1列(1つの値) | SELECT リスト、WHERE、HAVING | =、> などの比較演算子 |
| 列サブクエリ | 1列の複数行(値の一覧) | WHERE、HAVING | IN、NOT IN、ANY、ALL |
| 行サブクエリ | 複数列の1行、または複数列の一覧 | WHERE | (a, b) = (...)、(a, b) IN (...) |
| 表サブクエリ | 複数列・複数行 | FROM(派生テーブル)、EXISTS | EXISTS、JOIN |
もう1つの分け方が、相関するかどうかです。サブクエリの中で外側のクエリの列(c.id など)を参照しているものを相関サブクエリと呼びます。MySQL のリファレンスマニュアルは、相関サブクエリを「外側のクエリにも現れるテーブルへの参照を含むサブクエリ」と説明しています。相関しないサブクエリは外側と無関係に1回計算すれば済みますが、相関サブクエリは意味の上では外側の行ごとに評価されます。
スカラーサブクエリ - 1つの値として使う
WHERE で全体の平均と比べる
全注文の平均額より高い注文を出します。平均は (SELECT AVG(amount) FROM orders) という1つの値として比較に使えます。
SELECT id, customer_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY id;| id | customer_id | amount |
|---|---|---|
| 102 | 1 | 3000 |
| 104 | 2 | 2500 |
平均は 9000 / 5 = 1800 です。WHERE には集約関数を直接書けませんが(GROUP BY と HAVING の記事を参照)、サブクエリの中なら集約関数を使えます。サブクエリが先に1つの値を計算し、外側はそれを定数のように使うからです。
SELECT リストに書くと、各行の横に同じ値を並べられます。
SELECT id, amount, (SELECT AVG(amount) FROM orders) AS avg_amount
FROM orders
ORDER BY id;| id | amount | avg_amount |
|---|---|---|
| 101 | 1200 | 1800.0 |
| 102 | 3000 | 1800.0 |
| 103 | 800 | 1800.0 |
| 104 | 2500 | 1800.0 |
| 105 | 1500 | 1800.0 |
同じことは ウィンドウ関数 の AVG(amount) OVER () でも書けます。
SELECT に書く相関サブクエリ
顧客ごとの注文件数と最終注文日を、顧客の一覧に並べます。サブクエリの中で外側の c.id を参照しているので、相関サブクエリです。
SELECT c.id, c.name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_cnt,
(SELECT MAX(o.ordered_on) FROM orders o WHERE o.customer_id = c.id) AS last_ordered
FROM customers c
ORDER BY c.id;| id | name | order_cnt | last_ordered |
|---|---|---|---|
| 1 | 顧客A | 2 | 2026-11-05 |
| 2 | 顧客B | 2 | 2026-11-10 |
| 3 | 顧客C | 0 | NULL |
| 4 | 顧客D | 0 | NULL |
注文のない顧客C・顧客D も行として残り、件数は 0、最終注文日は NULL になります。LEFT JOIN と GROUP BY で書く場合と違い、COUNT(*) が 1 になる心配もありません。ただし、外側の行の数だけサブクエリが実行される形になりやすいので、行数が多いときの性能は後半の節で確認します。
0 行なら NULL、2 行以上なら製品で挙動が違う
スカラーサブクエリが1行も返さないと、結果は NULL です。
SELECT (SELECT amount FROM orders WHERE id = 999) AS r;| r |
|---|
| NULL |
MySQL のリファレンスマニュアルも、スカラーサブクエリの結果が空なら NULL になるので、元の列が NOT NULL でも NULL になりうる、と注意しています。
2 行以上返す場合は製品で挙動が分かれます。顧客ごとに「注文額」を1つ取ろうとして、注文が2件ある顧客に当たる例です。
SELECT c.id,
(SELECT o.amount FROM orders o WHERE o.customer_id = c.id) AS amt
FROM customers c
ORDER BY c.id;ERROR: more than one row returned by a subquery used as an expressionMySQL では ERROR 1242(Subquery returns more than 1 row)になることがマニュアルの「Subquery Errors」に載っています。一方、SQLite 3.54.0 はエラーにせず、次の結果を返しました。
| id | amt |
|---|---|
| 1 | 1200 |
| 2 | 800 |
| 3 | NULL |
| 4 | NULL |
SQLite のドキュメントは、サブクエリ式の値は SELECT の結果の最初の行になる、と定めています。ORDER BY のないサブクエリでは「最初の行」がどれになるかは決まっていないので、たまたま動いているだけです。1つに決めたいなら、MAX(o.amount) のように集約するか、ORDER BY ... LIMIT 1 を付けます。
スカラーサブクエリが2列以上を返すと、PostgreSQL は subquery must return only one column、SQLite は sub-select returns 2 columns - expected 1、MySQL は ERROR 1241(Operand should contain 1 column(s))になります。複数の列を外側の行ごとに取りたい場合は、後述の LATERAL を使います。
IN - 一覧に含まれるかを調べる
IN (SELECT ...) は、サブクエリが返す1列の一覧に、左辺の値が含まれるかを調べます。注文のある顧客を出します。
SELECT id, name
FROM customers
WHERE id IN (SELECT customer_id FROM orders)
ORDER BY id;| id | name |
|---|---|
| 1 | 顧客A |
| 2 | 顧客B |
JOIN との違い - 外側の行が増えない
同じことを JOIN で書くと、結果が変わります。
SELECT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id;| id | name |
|---|---|
| 1 | 顧客A |
| 1 | 顧客A |
| 2 | 顧客B |
| 2 | 顧客B |
JOIN は相手の行が2件あれば2行を作ります。IN や EXISTS は「相手が1件以上あるか」だけを見るので、外側の行は最大1回しか出ません。この「相手があるかだけを見て、外側の行を重複させない結合」をセミジョイン(半結合)と呼びます。JOIN の結果に DISTINCT を付けて重複を消すより、IN や EXISTS で書くほうが意図がはっきりします。JOIN で行が増える仕組みは SQL の JOIN の種類と違い で扱っています。
複数列の IN(行値)
左辺とサブクエリを複数列にすると、列の組み合わせで比べられます。顧客ごとの最高額の注文を出します。
SELECT id, customer_id, amount
FROM orders
WHERE (customer_id, amount) IN (
SELECT customer_id, MAX(amount)
FROM orders
GROUP BY customer_id
)
ORDER BY id;| id | customer_id | amount |
|---|---|---|
| 102 | 1 | 3000 |
| 104 | 2 | 2500 |
PostgreSQL 18.6 と SQLite 3.54.0 の両方で動きました。MySQL 8.4 のリファレンスマニュアルにも、行サブクエリ(Row Subqueries)の節があります。ゲスト購入の 105 は、サブクエリ側の組が (NULL, 1500) になり、NULL 同士の比較が真にならないので出てきません。
EXISTS - 1件でもあるかを調べる
EXISTS (SELECT ...) は、サブクエリが1行でも返せば真、1行も返さなければ偽です。2500 円以上の注文がある顧客を出します。
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.amount >= 2500
)
ORDER BY c.id;| id | name |
|---|---|
| 1 | 顧客A |
| 2 | 顧客B |
EXISTS のサブクエリは、ほとんどの場合 o.customer_id = c.id のように外側と相関させて使います。IN は「値が一覧に含まれるか」を1列で調べますが、EXISTS は WHERE に任意の条件を書けるので、複数の列や範囲の条件を組み合わせやすい書き方です。
SELECT リストの中身は結果に関係しない
EXISTS の結果は「行があるかどうか」だけで決まり、サブクエリが返す列の中身は関係しません。PostgreSQL のドキュメントは、結果は行が返るかどうかだけに依存し、行の中身には依存しないので、サブクエリの出力リストは通常重要ではない、とし、慣習として EXISTS(SELECT 1 WHERE ...) の形で書くことを紹介しています(INTERSECT を使うサブクエリなどの例外があるとも書いています)。MySQL のマニュアルも、伝統的には SELECT * で書き始めるが、SELECT 5 でも列名でも何でもよく、MySQL は SELECT リストを無視する、としています。
SELECT c.id
FROM customers c
WHERE EXISTS (SELECT NULL FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.id;| id |
|---|
| 1 |
| 2 |
返る行の値が NULL でも、行は存在するので EXISTS は真になります。
PostgreSQL のドキュメントは、サブクエリは通常、1行が返るかを判定できるところまでしか実行されず、最後までは実行されない、とも書いています。1件見つかった時点で打ち切れるのが EXISTS の性能上の利点です。
NOT IN と NULL の罠 - 否定には NOT EXISTS を使う
サブクエリでいちばん実害が出やすいのがここです。注文が1件もない顧客を NOT IN で出してみます。
SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders)
ORDER BY id;結果は 0 行です。顧客C と顧客D が出てくるはずなのに、何も返りません。
原因は、サブクエリの一覧にゲスト購入の NULL が含まれていることです。顧客C(id が 3)について展開すると、次のようになります。
3 <> 1 AND 3 <> 1 AND 3 <> 2 AND 3 <> 2 AND 3 <> NULL
= TRUE AND TRUE AND TRUE AND TRUE AND UNKNOWN
= UNKNOWNNULL との比較は UNKNOWN になり、WHERE は TRUE の行しか残さないので、顧客C は捨てられます。PostgreSQL のドキュメントは、NOT IN について「左辺が NULL の場合、または等しい値が右辺になく、右辺の行に少なくとも1つ NULL がある場合、結果は true ではなく null になる」と明記しています。実際に値を出して確かめます。
SELECT 3 NOT IN (SELECT customer_id FROM orders) AS r1,
1 NOT IN (SELECT customer_id FROM orders) AS r2;| r1 | r2 |
|---|---|
| NULL | 0 |
注文のない顧客(3)は NULL、注文のある顧客(1)は偽(SQLite は 0、PostgreSQL は f)です。一覧に NULL が1つでもあると、NOT IN は真を返さなくなります。3値論理の詳しい仕組みは SQL の NULL の扱い方 で扱っています。
回避方法1: NOT EXISTS を使う
いちばん確実なのは NOT EXISTS で書くことです。
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
)
ORDER BY c.id;| id | name |
|---|---|
| 3 | 顧客C |
| 4 | 顧客D |
NOT EXISTS は「条件に合う行があるかどうか」の真偽しか返さないので、UNKNOWN が入り込みません。ゲスト購入の 105 は o.customer_id = c.id が UNKNOWN になり、単に「合う行」に数えられないだけです。
回避方法2: サブクエリから NULL を除く
NOT IN のまま、サブクエリ側で NULL を除いても正しく動きます。
SELECT id, name
FROM customers
WHERE id NOT IN (
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
)
ORDER BY id;| id | name |
|---|---|
| 3 | 顧客C |
| 4 | 顧客D |
ただし、列が NULL を許す限り、条件を付け忘れると同じ問題が再発します。サブクエリの列が NOT NULL 制約付きで NULL になりえないと分かっている場合以外は、NOT EXISTS を基本にするほうが安全です。
回避方法3: LEFT JOIN と IS NULL
外部結合して、相手がいなかった行だけを残す書き方もあります。
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;| id | name |
|---|---|
| 3 | 顧客C |
| 4 | 顧客D |
IS NULL で調べる列は、主キーや結合に使った列のように、相手がいれば必ず値が入っている列を選びます。
左辺が NULL の行も、IN と NOT IN のどちらにも入らない
逆向きの例も見ておきます。注文を起点に、「東京の顧客の注文」と「大阪の顧客ではない注文」を出します。
SELECT id, customer_id
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE city = '東京')
ORDER BY id;| id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
SELECT id, customer_id
FROM orders
WHERE customer_id NOT IN (SELECT id FROM customers WHERE city = '大阪')
ORDER BY id;| id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
ゲスト購入の 105 は、どちらの結果にも出てきません。左辺が NULL だと IN も NOT IN も NULL になるからです。「大阪の顧客ではない注文」にゲスト購入を含めたいなら、customer_id IS NULL OR customer_id NOT IN (...) のように NULL の扱いを明示するか、NOT EXISTS で書きます。
相関サブクエリ - 外側の行ごとに条件を変える
相関サブクエリを WHERE に書くと、「その行が属するグループの中での条件」を表せます。顧客ごとに、その顧客の最高額と同じ金額の注文を出します。
SELECT o.id, o.customer_id, o.amount
FROM orders o
WHERE o.amount = (
SELECT MAX(o2.amount)
FROM orders o2
WHERE o2.customer_id = o.customer_id
)
ORDER BY o.id;| id | customer_id | amount |
|---|---|---|
| 102 | 1 | 3000 |
| 104 | 2 | 2500 |
外側の注文 101 を評価するときは、サブクエリが customer_id = 1 の最大額 3000 を返し、1200 = 3000 は偽なので捨てられます。102 では 3000 = 3000 で残ります。このように、相関サブクエリは外側の行の値を受け取って、その行専用の値を計算します。
ゲスト購入の 105 は、サブクエリの条件が o2.customer_id = NULL になって1行も合わず、MAX が NULL を返すので残りません。前の節の行値の IN と同じ結果です。同じ金額の注文が2件あれば両方残る点も、IN 版と同じです。1件に絞りたい場合は、ウィンドウ関数 の ROW_NUMBER() か、後述の LATERAL を使います。
二重の NOT EXISTS を使うと、「すべての〜について条件を満たす」という問い合わせも書けます。MySQL のマニュアルには、「すべての都市に出店している店舗の種類」を NOT EXISTS の中に NOT EXISTS を入れて求める例が載っています(「出店していない都市が存在しない」と言い換える書き方です)。
FROM 句のサブクエリ(派生テーブル)
FROM にサブクエリを書くと、その結果を1つの表として扱えます。これを派生テーブル(derived table)と呼びます。集計した結果をもう一度集計したいときの基本形です。
SELECT AVG(total) AS avg_total, MAX(total) AS max_total
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
) AS t;| avg_total | max_total |
|---|---|
| 3750.0 | 4200 |
顧客A の合計は 4200、顧客B は 3300 で、その平均が 3750 です。AVG(SUM(amount)) のように集約関数を入れ子にするとエラーになるので、内側で集計してから外側でもう一度集計します。
集計した結果を別のテーブルと結合することもできます。
SELECT c.name, t.total
FROM customers c
JOIN (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) AS t ON t.customer_id = c.id
ORDER BY c.id;| name | total |
|---|---|
| 顧客A | 4200 |
| 顧客B | 3300 |
1対多のテーブルを複数つなぐと行が掛け算で増えて SUM が膨らみますが、このように結合する前に集計しておくと防げます。
別名の要否は製品で違う
派生テーブルに付けた AS t の別名は、製品によって必須かどうかが違います。
| 製品 | 派生テーブルの別名 | 根拠 |
|---|---|---|
| MySQL 8.4 | 必須 | リファレンスマニュアルに、FROM 句のすべてのテーブルには名前が必要なので [AS] tbl_name は必須、とある |
| PostgreSQL 16 以降 | 省略可 | 16 のリリースノートに「FROM 句のサブクエリで別名を省略できるようにした」とある。手元の 18.6 でも省略して動いた |
| SQLite 3.54.0 | 省略可 | 手元で省略して動いた(ドキュメントでの位置付けは未確認) |
PostgreSQL のドキュメントは、標準 SQL ではサブクエリに別名が必要で、PostgreSQL は省略を許すが、他のシステムへ移植する可能性のあるコードでは書いておくのがよい、としています。別名は常に付けておくのが無難です。
ANY・SOME・ALL - 一覧のどれか・すべてと比べる
ANY と ALL は、比較演算子とサブクエリの一覧を組み合わせます。SOME は ANY の別名です。顧客B の注文額(800 と 2500)と比べてみます。
SELECT id, amount
FROM orders
WHERE amount > ANY (SELECT amount FROM orders WHERE customer_id = 2)
ORDER BY id;| id | amount |
|---|---|
| 101 | 1200 |
| 102 | 3000 |
| 104 | 2500 |
| 105 | 1500 |
> ANY は「一覧のどれか1つより大きい」、つまり最小値の 800 より大きい注文です。
SELECT id, amount
FROM orders
WHERE amount > ALL (SELECT amount FROM orders WHERE customer_id = 2)
ORDER BY id;| id | amount |
|---|---|
| 102 | 3000 |
> ALL は「一覧のすべてより大きい」、つまり最大値の 2500 より大きい注文です。
IN と NOT IN は、それぞれ次の書き方と同じ意味です。PostgreSQL のドキュメントも MySQL のマニュアルも、IN は = ANY と同じ、NOT IN は <> ANY ではなく <> ALL と同じだと書いています。
| 書き方 | 同じ意味 | 読み方 |
|---|---|---|
x IN (サブクエリ) | x = ANY (サブクエリ) | どれかと等しい |
x NOT IN (サブクエリ) | x <> ALL (サブクエリ) | すべてと等しくない |
<> ALL で書いても、NULL を含む一覧に対しては NOT IN と同じく 0 行になります(PostgreSQL 18.6 で確認)。
ALL は一覧が空だと真になる
ALL には、一覧が空のときに真になるという性質があります。注文のない顧客C の注文額と比べてみます。
SELECT id, amount
FROM orders
WHERE amount > ALL (SELECT amount FROM orders WHERE customer_id = 3)
ORDER BY id;| id | amount |
|---|---|
| 101 | 1200 |
| 102 | 3000 |
| 103 | 800 |
| 104 | 2500 |
| 105 | 1500 |
全件が返りました。PostgreSQL のドキュメントは、ALL はすべての行で真なら真(サブクエリが行を返さない場合を含む)と定めています。「比べる相手が1つもない」ので、反例も存在しない、という扱いです。
同じことを MAX で書くと、結果が逆になります。
SELECT id, amount
FROM orders
WHERE amount > (SELECT MAX(amount) FROM orders WHERE customer_id = 3)
ORDER BY id;結果は 0 行です。対象の行がないと MAX は NULL を返し、NULL との比較は UNKNOWN になるからです。MySQL のマニュアルも、テーブルが空なら 1 > ALL (SELECT s1 FROM t2) は真になるが、1 > (SELECT s1 FROM t2) や 1 > ALL (SELECT MAX(s1) FROM t2) は NULL になる、という例を挙げ、NULL を含むテーブルと空のテーブルは「エッジケース」なので常に考慮するよう注意しています。> ALL を > (SELECT MAX(...)) に書き換えると、空のときの意味が変わる点に注意します。
製品ごとの対応
| 製品 | ANY / SOME / ALL |
|---|---|
| PostgreSQL 18.6 | 使える(手元で実行) |
| MySQL 8.4 | 使える(リファレンスマニュアルに「Subqueries with ANY, IN, or SOME」「Subqueries with ALL」の節がある) |
| SQLite 3.54.0 | 使えない(near "ALL": syntax error) |
SQLite では、> ALL を NOT EXISTS (SELECT 1 FROM ... WHERE amount >= 外側の値) のように書き換えます。この形なら空集合のときの意味(真になる)も保たれます。
LATERAL - FROM 句で左側のテーブルを参照する
通常の派生テーブルは、同じ FROM 句にある他のテーブルの列を参照できません。顧客ごとに最新の注文を1件、注文番号・金額・日付の3列で取ろうとして、派生テーブルの中から c.id を参照するとエラーになります。
SELECT c.name, lo.amount
FROM customers c
CROSS JOIN (
SELECT o.amount FROM orders o WHERE o.customer_id = c.id LIMIT 1
) AS lo;ERROR: invalid reference to FROM-clause entry for table "c"
DETAIL: There is an entry for table "c", but it cannot be referenced from this part of the query.
HINT: To reference that table, you must mark this subquery with LATERAL.派生テーブルの前に LATERAL を付けると、左側にあるテーブルの列を参照できるようになります。PostgreSQL のドキュメントによると、LATERAL の付いたサブクエリは、参照している列を提供する行ごとに評価され、その結果の行が元の行と結合されます。
SELECT c.id, c.name, lo.id AS order_id, lo.amount, lo.ordered_on
FROM customers c
CROSS JOIN LATERAL (
SELECT o.id, o.amount, o.ordered_on
FROM orders o
WHERE o.customer_id = c.id
ORDER BY o.ordered_on DESC
LIMIT 1
) AS lo
ORDER BY c.id;| id | name | order_id | amount | ordered_on |
|---|---|---|---|---|
| 1 | 顧客A | 102 | 3000 | 2026-11-05 |
| 2 | 顧客B | 104 | 2500 | 2026-11-10 |
スカラーサブクエリでは1列しか取れませんでしたが、LATERAL なら複数の列も複数の行も取れます。LIMIT 3 にすれば「顧客ごとの直近3件」になります。
LEFT JOIN LATERAL ... ON true で相手のない行も残す
CROSS JOIN LATERAL は、サブクエリが1行も返さない顧客(C と D)を落とします。残したい場合は LEFT JOIN LATERAL にして、結合条件に ON true を書きます。
SELECT c.id, c.name, lo.id AS order_id, lo.amount, lo.ordered_on
FROM customers c
LEFT JOIN LATERAL (
SELECT o.id, o.amount, o.ordered_on
FROM orders o
WHERE o.customer_id = c.id
ORDER BY o.ordered_on DESC
LIMIT 1
) AS lo ON true
ORDER BY c.id;| id | name | order_id | amount | ordered_on |
|---|---|---|---|---|
| 1 | 顧客A | 102 | 3000 | 2026-11-05 |
| 2 | 顧客B | 104 | 2500 | 2026-11-10 |
| 3 | 顧客C | NULL | NULL | NULL |
| 4 | 顧客D | NULL | NULL | NULL |
PostgreSQL のドキュメントも、LATERAL のサブクエリへ LEFT JOIN すると、サブクエリが行を返さない元の行も結果に出せるので特に便利だ、と紹介しています。結合の条件はサブクエリの中の WHERE で済ませているので、ON には常に真の true を書きます。
MySQL 8.4 のリファレンスマニュアルにも「Lateral Derived Tables」の節があり、営業担当者ごとに最大の売上とその顧客名を取る例で、LATERAL がないと Unknown column のエラー(ERROR 1054)になり、付けると通ることが説明されています。
LATERAL がない製品ではウィンドウ関数で書く
SQLite 3.54.0 は LATERAL に対応しておらず、構文エラーになります。「グループごとの上位 N 件」は、派生テーブルの中で ROW_NUMBER() を付けてから絞る形で書けます。
SELECT c.id, c.name, x.id AS order_id, x.amount, x.ordered_on
FROM customers c
LEFT JOIN (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_on DESC) AS rn
FROM orders o
) AS x ON x.customer_id = c.id AND x.rn = 1
ORDER BY c.id;| id | name | order_id | amount | ordered_on |
|---|---|---|---|---|
| 1 | 顧客A | 102 | 3000 | 2026-11-05 |
| 2 | 顧客B | 104 | 2500 | 2026-11-10 |
| 3 | 顧客C | NULL | NULL | NULL |
| 4 | 顧客D | NULL | NULL | NULL |
LEFT JOIN LATERAL と同じ結果です。こちらは PostgreSQL・MySQL・SQLite のいずれでも使える書き方です。どちらが速いかは、外側の行数・1行あたりの相手の行数・インデックスの有無で変わります。外側が少なく、(customer_id, ordered_on) のインデックスがあるなら LATERAL の LIMIT 1 はインデックスを少し読むだけで済み、外側が多いなら全体を1回並べ替えるウィンドウ関数のほうが有利になりやすい、というのが目安です(実際は実行計画で確かめます)。
JOIN・CTE との使い分け
同じ結果は、サブクエリでも JOIN でも CTE でも書けることが多くあります。迷ったときの目安をまとめます。
| やりたいこと | 向いている書き方 | 理由 |
|---|---|---|
| 相手のテーブルの列も結果に出したい | JOIN | サブクエリ(IN・EXISTS)の中の列は外へ出せない |
| 相手があるかないかで絞るだけ | EXISTS / NOT EXISTS(または IN) | 外側の行が重複しない。否定は NOT EXISTS |
| 1つの値(平均・最大)と比べる | スカラーサブクエリ | 値として比較に直接使える |
| 集計結果をさらに集計・結合する | 派生テーブルか CTE | 集約関数の入れ子はエラーになる |
| 同じ途中結果を2回以上使う | CTE | 派生テーブルは書いた場所でしか使えない |
| 外側の行ごとに複数列・上位 N 件を取る | LATERAL かウィンドウ関数 | スカラーサブクエリは1行1列しか返せない |
| 明細の各行に集計値を並べる | ウィンドウ関数、相関サブクエリ | 行数を減らさずに集計値を付けられる |
CTE は、派生テーブルに名前を付けてクエリの先頭へ出したものと考えられます。たとえば前の節の「顧客ごとの合計を顧客名と結合する」クエリは、次のように書いても同じ結果です。
WITH t AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT c.name, t.total
FROM customers c
JOIN t ON t.customer_id = c.id
ORDER BY c.id;サブクエリが2段・3段と入れ子になって読みにくくなってきたら、CTE に切り出すと上から順に読めるようになります。CTE がどう実行されるか(インライン化されるか、一度だけ計算されるか)は製品とバージョンで違うので、SQL の WITH 句(CTE)入門 の「パフォーマンス」の節を参照してください。
UPDATE・DELETE でのサブクエリ
サブクエリは UPDATE や DELETE の条件にも書けます。注文が1件もない顧客を削除する例です。
DELETE FROM customers
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = customers.id
);SQLite 3.54.0 では顧客C と顧客D の2行が削除されました。ここでも NOT IN で書くと、orders.customer_id に NULL があるために1行も削除されません。SELECT なら結果が空なことに気付けますが、DELETE や UPDATE では「何も起きなかった」ことに気付きにくいので、否定の条件は特に NOT EXISTS で書きます。
MySQL には、更新・削除の対象と同じテーブルをサブクエリで読めないという制限があります。MySQL 8.4 のリファレンスマニュアルの「Restrictions on Subqueries」は、一般にテーブルを変更しながら同じテーブルをサブクエリで SELECT することはできないとして、次の形を挙げています。
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);マニュアルには、サブクエリを派生テーブルとして書き、それがマテリアライズされる場合は例外になることも書かれています。この制限は PostgreSQL と SQLite にはなく、上の形は手元の PostgreSQL 18.6 でも SQLite 3.54.0 でもそのまま実行できます。
パフォーマンス - セミジョインとアンチジョインへの変換
意味の上では、相関サブクエリは外側の行ごとに評価されます。しかし実際のデータベースは、多くの場合サブクエリを結合に書き換えてから実行します。
IN/EXISTSはセミジョイン(相手があれば外側の行を1回だけ出す結合)にNOT EXISTSはアンチジョイン(相手がない外側の行だけを出す結合)に
結合に書き換えられれば、ハッシュ結合やインデックスを使った結合など、JOIN と同じアルゴリズムから最も安いものを選べます。書き換えられないと、外側の行ごとにサブクエリを実行する形が残ります。
以下は、手元の PostgreSQL 18.6 に顧客 1 万行(big_customers)、注文 20 万行(big_orders、customer_id は 1〜7000 で、50 件に1件は NULL)を入れ、big_orders.customer_id にインデックスを作って確かめた実行計画です。
IN と EXISTS は Semi Join になる
EXPLAIN (COSTS OFF)
SELECT * FROM big_customers c
WHERE c.id IN (SELECT customer_id FROM big_orders);
EXPLAIN (COSTS OFF)
SELECT * FROM big_customers c
WHERE EXISTS (SELECT 1 FROM big_orders o WHERE o.customer_id = c.id); Nested Loop Semi Join
-> Seq Scan on big_customers c
-> Index Only Scan using big_orders_customer_id on big_orders o
Index Cond: (customer_id = c.id)IN と EXISTS は、同じ Nested Loop Semi Join になりました。顧客1人ごとにインデックスを引き、1件見つかった時点で次の顧客へ進みます。「IN より EXISTS が速い」と言われることがありますが、少なくともこの単純な形では、PostgreSQL はどちらも同じ計画にしています。
PostgreSQL 17 のリリースノートには「相関のある IN サブクエリを結合に変換できるようにした」という項目があります。手元の 18.6 でも、サブクエリの中で外側の列を参照する IN が、SubPlan ではなく Hash Join の計画になることを確認しました。
NOT EXISTS は Anti Join になる
EXPLAIN (COSTS OFF)
SELECT * FROM big_customers c
WHERE NOT EXISTS (SELECT 1 FROM big_orders o WHERE o.customer_id = c.id); Nested Loop Anti Join
-> Seq Scan on big_customers c
-> Index Only Scan using big_orders_customer_id on big_orders o
Index Cond: (customer_id = c.id)LEFT JOIN ... WHERE o.customer_id IS NULL(結合に使った列で判定する形)も、手元では同じ Nested Loop Anti Join になりました。一方、WHERE o.id IS NULL(主キーで判定する形)では Hash Right Join の後に Filter: (o.id IS NULL) で絞る計画になり、アンチジョインには変換されていませんでした。結果は同じですが、計画は書き方で変わることがあります。
NOT IN はアンチジョインにならない
EXPLAIN (COSTS OFF)
SELECT * FROM big_customers c
WHERE c.id NOT IN (SELECT customer_id FROM big_orders); Seq Scan on big_customers c
Filter: (NOT (ANY (id = (hashed SubPlan 1).col1)))
SubPlan 1
-> Seq Scan on big_ordersNOT IN は結合に変換されず、サブクエリの結果をハッシュ表にして(hashed SubPlan)、顧客の各行をフィルタで判定しています。NULL があると結果が UNKNOWN になるという NOT IN の意味を、アンチジョインでは表せないためです。サブクエリに WHERE customer_id IS NOT NULL を付けても、手元の 18.6 では同じ hashed SubPlan のままでした。
さらに、work_mem を 64kB に下げてサブクエリの結果がハッシュ表に収まらないようにすると、計画は次のように変わりました。
Seq Scan on big_customers c
Filter: (NOT (ANY (id = (SubPlan 1).col1)))
SubPlan 1
-> Materialize
-> Seq Scan on big_ordershashed が消え、顧客1行ごとにマテリアライズした 20 万行を順に調べる形です。行数が増えるほど急激に遅くなる計画です。参考までに、通常の設定で1回ずつ実行した時間は、NOT EXISTS が約 10ms、IS NOT NULL 付きの NOT IN が約 59ms でした(手元のノート PC で1回ずつ測っただけの値で、ベンチマークではありません)。正しさの面でも速さの面でも、否定は NOT EXISTS で書くのが基本です。
SELECT リストの相関サブクエリは行ごとの SubPlan になる
EXPLAIN (COSTS OFF)
SELECT c.id, (SELECT COUNT(*) FROM big_orders o WHERE o.customer_id = c.id)
FROM big_customers c;
EXPLAIN (COSTS OFF)
SELECT c.id, COUNT(o.id)
FROM big_customers c
LEFT JOIN big_orders o ON o.customer_id = c.id
GROUP BY c.id; Seq Scan on big_customers c
SubPlan 1
-> Aggregate
-> Bitmap Heap Scan on big_orders o
Recheck Cond: (customer_id = c.id)
-> Bitmap Index Scan on big_orders_customer_id
Index Cond: (customer_id = c.id) HashAggregate
Group Key: c.id
-> Hash Right Join
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on big_orders o
-> Hash
-> Seq Scan on big_customers cSELECT リストに書いた相関サブクエリは、顧客1行ごとに SubPlan 1 を実行する計画になりました。インデックスがあるので1回あたりは軽いのですが、外側の行数に比例して回数が増えます。LEFT JOIN と GROUP BY で書くと、両方のテーブルを1回ずつ読んでハッシュ結合と集計で済ませる計画になります。外側の行を LIMIT で数十件に絞って表示するような場面なら相関サブクエリでも問題になりにくく、全件を集計するなら結合と GROUP BY が有利になりやすい、というのが目安です。
アプリケーション側のループで顧客ごとにクエリを発行するのは、この SubPlan をデータベースの外で、しかも通信を挟んで繰り返すのと同じ構造です。N+1 問題 として知られるもので、1回のクエリにまとめる価値はさらに大きくなります。
MySQL 8.4 の semijoin・antijoin 変換
MySQL 8.4 のリファレンスマニュアル「Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin」によると、MySQL も IN・= ANY・EXISTS のサブクエリをセミジョインに、その否定をアンチジョインに変換します。
- 対象は、
WHEREかONの最上位(ANDの項を含む)にあるIN・= ANY・EXISTS。EXISTSは同等のINと同じ変換の対象になる - サブクエリは
UNIONを含まない単一のSELECTで、HAVING・集約関数・LIMITを含まないこと。DISTINCT・ORDER BYは許されるが無視される NOT IN (SELECT ...)、NOT EXISTS (SELECT ...)などの否定はアンチジョインに変換される。ただし、比較する式が NULL になりうる場合は、ほとんどの場合アンチジョインに変換できない- セミジョインの実行方法は、Table pullout(結合への変換)、Duplicate Weedout、FirstMatch、LooseScan、Materialization からコストで選ぶ。
optimizer_switchのsemijoin(既定で有効)がセミジョインとアンチジョインの両方を制御する
EXPLAIN では、マニュアルによると次のように見えます。
| 方式 | EXPLAIN での見え方 |
|---|---|
| Table pullout | EXPLAIN の後の SHOW WARNINGS で、サブクエリが消えて外側の結合に統合されたクエリが見える |
| Duplicate Weedout | Extra 列に Start temporary と End temporary |
| FirstMatch | Extra 列に FirstMatch(tbl_name) |
| LooseScan | Extra 列に LooseScan(m..n) |
| Materialization | select_type が MATERIALIZED、table が <subqueryN> の行 |
NULL になりうる列ではアンチジョインに変換しにくいという点は、NOT IN の罠と同じ根っこです。MySQL のマニュアル「Optimizing Subqueries with the EXISTS Strategy」も、IN のサブクエリで NULL と FALSE を区別する必要があると、OR inner_expr IS NULL のような追加の条件を調べることになり、実行が少し複雑になると説明しています。結合や比較に使う列に NOT NULL 制約を付けておくことは、正しさだけでなく最適化の面でも効きます。
なお MySQL 8.4 には、SELECT・WHERE・JOIN・HAVING 句のスカラーサブクエリを派生テーブルへの外部結合に変換する subquery_to_derived という optimizer_switch のフラグもあります。マニュアルによると既定は無効です。
SQLite の EXPLAIN QUERY PLAN
SQLite 3.54.0 でサンプルの orders.customer_id にインデックスを作り、EXPLAIN QUERY PLAN で確かめると次のようになりました。
QUERY PLAN
|--SCAN c
`--CORRELATED SCALAR SUBQUERY 1
`--SEARCH o USING COVERING INDEX orders_customer_id (customer_id=?)QUERY PLAN
|--SEARCH customers USING INTEGER PRIMARY KEY (rowid=?)
`--LIST SUBQUERY 1
`--SCAN orders USING COVERING INDEX orders_customer_idNOT EXISTS は CORRELATED SCALAR SUBQUERY として外側の行ごとにインデックスを引く形、相関しない IN は LIST SUBQUERY としてサブクエリの一覧を先に作り、主キーで顧客を引く形です。SQLite では、相関サブクエリの中の検索条件にインデックスがあるかどうかが特に効きます。インデックスの効く条件・効かない条件は データベースインデックス入門 で扱っています。
よくあるエラーと対処
| 症状・エラー | PostgreSQL 18.6 | SQLite 3.54.0 | MySQL 8.4(マニュアル) | 対処 |
|---|---|---|---|---|
| スカラーサブクエリが2行以上返す | more than one row returned by a subquery used as an expression | エラーにならず先頭行を使う | ERROR 1242 Subquery returns more than 1 row | 集約するか ORDER BY ... LIMIT 1 |
| スカラーサブクエリが2列以上返す | subquery must return only one column | sub-select returns 2 columns - expected 1 | ERROR 1241 Operand should contain 1 column(s) | 列を1つにするか LATERAL |
| 派生テーブルから外側の列を参照 | invalid reference to FROM-clause entry(LATERAL を付けるよう HINT が出る) | no such column | ERROR 1054 Unknown column | LATERAL を付ける |
NOT IN が 0 行になる | エラーにならない | エラーにならない | エラーにならない | NOT EXISTS に書き換える |
IN のサブクエリに LIMIT | 使える | 使える | ERROR 1235(LIMIT & IN/ALL/ANY/SOME subquery は未対応) | 派生テーブルで包む |
ANY / ALL | 使える | 構文エラー | 使える | SQLite は EXISTS / NOT EXISTS で書き換える |
| 派生テーブルに別名がない | 16 以降は省略可 | 省略可 | 別名は必須 | 常に AS t を付ける |
MySQL の ERROR 1235 は、マニュアルの「Subquery Errors」に SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1) のような文はサポートしない、と例付きで載っています。
いちばん厄介なのは、エラーにならずに結果だけが変わる2つです。SQLite のスカラーサブクエリが黙って先頭行を使う挙動と、NOT IN の NULL の罠は、どちらもクエリが「動いてしまう」ので、テストデータに NULL や重複がないと気付けません。サブクエリを書いたら、一覧に NULL が入る場合と、0 件・2 件以上になる場合の結果を一度確かめておくと安心です。
まとめ
- サブクエリは、返す形(1つの値・1列の一覧・表)と、外側の列を参照するか(相関するか)で性質が決まる。
- スカラーサブクエリは1つの値として比較や
SELECTリストに使える。0 行なら NULL、2 行以上は PostgreSQL・MySQL でエラー、SQLite は先頭行を黙って使う。 INとEXISTSは「相手があるか」だけを見るセミジョインで、JOINと違って外側の行が重複しない。EXISTSのサブクエリのSELECTリストは結果に影響しない。NOT INは、サブクエリの一覧に NULL が1つでもあると真を返さず、0 行になる。左辺が NULL の行もINとNOT INのどちらにも入らない。否定はNOT EXISTSで書く。- 相関サブクエリは外側の行ごとに評価される。グループごとの最大の行や「すべての〜について」の条件を書ける。
FROM句のサブクエリ(派生テーブル)で、集計結果をさらに集計・結合できる。別名は MySQL では必須なので、常に付ける。INは= ANY、NOT INは<> ALLと同じ。ALLは一覧が空だと真になり、> (SELECT MAX(...))とは空のときの結果が逆になる。SQLite はANY・ALLに非対応。- 外側の行ごとに複数列・上位 N 件を取るなら
LEFT JOIN LATERAL (...) ON true。SQLite ではROW_NUMBER()で代用する。 - PostgreSQL 18.6 では
INとEXISTSが同じ Semi Join、NOT EXISTSが Anti Join になり、NOT INは結合に変換されずhashed SubPlanになった。MySQL 8.4 も NULL になりうる列ではアンチジョインにほとんど変換できない。結合に使う列にはNOT NULLを付ける。 SELECTリストの相関サブクエリは外側の行ごとに実行されやすい。全件を集計するならJOINとGROUP BYも比べる。
サブクエリが思った結果にならないときは、まずサブクエリだけを取り出して単独で実行し、何行・どんな値(特に NULL)が返っているかを確かめてみてください。IN や NOT IN の右側に何が並んでいるかが見えれば、多くの問題はそこで原因がわかります。
参考リンク
- Subquery Expressions(PostgreSQL 公式)
- Table Expressions - Subqueries / LATERAL Subqueries(PostgreSQL 公式)
- Using EXPLAIN(PostgreSQL 公式)
- PostgreSQL 16 Release Notes
- PostgreSQL 17 Release Notes
- Subqueries(MySQL 8.4 リファレンスマニュアル)
- Subqueries with ANY, IN, or SOME(MySQL 8.4 リファレンスマニュアル)
- Subqueries with ALL(MySQL 8.4 リファレンスマニュアル)
- Subqueries with EXISTS or NOT EXISTS(MySQL 8.4 リファレンスマニュアル)
- Correlated Subqueries(MySQL 8.4 リファレンスマニュアル)
- Derived Tables(MySQL 8.4 リファレンスマニュアル)
- Lateral Derived Tables(MySQL 8.4 リファレンスマニュアル)
- Subquery Errors(MySQL 8.4 リファレンスマニュアル)
- Restrictions on Subqueries(MySQL 8.4 リファレンスマニュアル)
- Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin(MySQL 8.4 リファレンスマニュアル)
- Optimizing Subqueries with the EXISTS Strategy(MySQL 8.4 リファレンスマニュアル)
- Switchable Optimizations(MySQL 8.4 リファレンスマニュアル)
- SQL Language Expressions(SQLite 公式)
- EXPLAIN QUERY PLAN(SQLite 公式)
関連記事
- SQL の NULL の扱い方 - 3値論理と NOT IN の落とし穴、IS NULL・COALESCE の使い分け
- SQL の JOIN の種類と違い - INNER / LEFT / FULL / CROSS の結果を例で理解する
- SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序
- SQL の WITH 句(CTE)入門 - 共通テーブル式の書き方と再帰 CTE で階層データをたどる方法
- SQL ウィンドウ関数入門 - OVER と PARTITION BY、ROW_NUMBER・RANK・LAG の使い分け
- クエリ実行計画入門 - EXPLAINの読み方とプランナがJOINアルゴリズムを選ぶ理由
- データベースインデックス入門 - B-treeの仕組みと、効くクエリ・効かないクエリ
- N+1問題とは - ORM で起きる原因と Eager Loading・JOIN・バッチ取得での対策


