SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴

SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴

作成日:
読了:約58分
更新日:

当サイトは 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 truePostgreSQL・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 テーブル:

idnamecity
1顧客A東京
2顧客B大阪
3顧客C東京
4顧客D名古屋

orders テーブル:

idcustomer_idamountordered_on
101112002026-11-01
102130002026-11-05
10328002026-11-03
104225002026-11-10
105NULL15002026-11-08

ポイントは次の3つです。

  • 顧客A と顧客B には注文が2件ずつある
  • 顧客C と顧客D には注文が1件もない
  • 注文 105 はゲスト購入で、customer_id が NULL

サブクエリとは - クエリの中に書くクエリ

サブクエリは、かっこで囲んで別のクエリの中に書いた SELECT 文です。外側のクエリから見ると、サブクエリは「値」か「一覧」か「表」として扱われます。

種類返すもの主な書き場所組み合わせる演算子
スカラーサブクエリ1行1列(1つの値)SELECT リスト、WHERE、HAVING=、> などの比較演算子
列サブクエリ1列の複数行(値の一覧)WHERE、HAVINGIN、NOT IN、ANY、ALL
行サブクエリ複数列の1行、または複数列の一覧WHERE(a, b) = (...)、(a, b) IN (...)
表サブクエリ複数列・複数行FROM(派生テーブル)、EXISTSEXISTS、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;
idcustomer_idamount
10213000
10422500

平均は 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;
idamountavg_amount
10112001800.0
10230001800.0
1038001800.0
10425001800.0
10515001800.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;
idnameorder_cntlast_ordered
1顧客A22026-11-05
2顧客B22026-11-10
3顧客C0NULL
4顧客D0NULL

注文のない顧客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件ある顧客に当たる例です。

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;
エラー(PostgreSQL 18.6)
ERROR:  more than one row returned by a subquery used as an expression

MySQL では ERROR 1242(Subquery returns more than 1 row)になることがマニュアルの「Subquery Errors」に載っています。一方、SQLite 3.54.0 はエラーにせず、次の結果を返しました。

idamt
11200
2800
3NULL
4NULL

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列の一覧に、左辺の値が含まれるかを調べます。注文のある顧客を出します。

注文のある顧客(IN)
SELECT id, name
FROM customers
WHERE id IN (SELECT customer_id FROM orders)
ORDER BY id;
idname
1顧客A
2顧客B

JOIN との違い - 外側の行が増えない

同じことを JOIN で書くと、結果が変わります。

注文のある顧客(JOIN)
SELECT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id;
idname
1顧客A
1顧客A
2顧客B
2顧客B

JOIN は相手の行が2件あれば2行を作ります。IN や EXISTS は「相手が1件以上あるか」だけを見るので、外側の行は最大1回しか出ません。この「相手があるかだけを見て、外側の行を重複させない結合」をセミジョイン(半結合)と呼びます。JOIN の結果に DISTINCT を付けて重複を消すより、IN や EXISTS で書くほうが意図がはっきりします。JOIN で行が増える仕組みは SQL の JOIN の種類と違い で扱っています。

複数列の IN(行値)

左辺とサブクエリを複数列にすると、列の組み合わせで比べられます。顧客ごとの最高額の注文を出します。

顧客ごとの最高額の注文(行値の 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;
idcustomer_idamount
10213000
10422500

PostgreSQL 18.6 と SQLite 3.54.0 の両方で動きました。MySQL 8.4 のリファレンスマニュアルにも、行サブクエリ(Row Subqueries)の節があります。ゲスト購入の 105 は、サブクエリ側の組が (NULL, 1500) になり、NULL 同士の比較が真にならないので出てきません。

EXISTS - 1件でもあるかを調べる

EXISTS (SELECT ...) は、サブクエリが1行でも返せば真、1行も返さなければ偽です。2500 円以上の注文がある顧客を出します。

2500 円以上の注文がある顧客(EXISTS)
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;
idname
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 NULL でも結果は同じ
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 で出してみます。

注文のない顧客を 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 NOT IN (1, 1, 2, 2, NULL) の展開
3 <> 1 AND 3 <> 1 AND 3 <> 2 AND 3 <> 2 AND 3 <> NULL
= TRUE AND TRUE AND TRUE AND TRUE AND UNKNOWN
= UNKNOWN

NULL との比較は UNKNOWN になり、WHERE は TRUE の行しか残さないので、顧客C は捨てられます。PostgreSQL のドキュメントは、NOT IN について「左辺が NULL の場合、または等しい値が右辺になく、右辺の行に少なくとも1つ NULL がある場合、結果は true ではなく null になる」と明記しています。実際に値を出して確かめます。

NOT IN の結果を直接見る
SELECT 3 NOT IN (SELECT customer_id FROM orders) AS r1,
       1 NOT IN (SELECT customer_id FROM orders) AS r2;
r1r2
NULL0

注文のない顧客(3)は NULL、注文のある顧客(1)は偽(SQLite は 0、PostgreSQL は f)です。一覧に NULL が1つでもあると、NOT IN は真を返さなくなります。3値論理の詳しい仕組みは SQL の NULL の扱い方 で扱っています。

回避方法1: NOT EXISTS を使う

いちばん確実なのは 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;
idname
3顧客C
4顧客D

NOT EXISTS は「条件に合う行があるかどうか」の真偽しか返さないので、UNKNOWN が入り込みません。ゲスト購入の 105 は o.customer_id = c.id が UNKNOWN になり、単に「合う行」に数えられないだけです。

回避方法2: サブクエリから NULL を除く

NOT IN のまま、サブクエリ側で 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;
idname
3顧客C
4顧客D

ただし、列が NULL を許す限り、条件を付け忘れると同じ問題が再発します。サブクエリの列が NOT NULL 制約付きで NULL になりえないと分かっている場合以外は、NOT EXISTS を基本にするほうが安全です。

回避方法3: LEFT JOIN と IS NULL

外部結合して、相手がいなかった行だけを残す書き方もあります。

注文のない顧客(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;
idname
3顧客C
4顧客D

IS NULL で調べる列は、主キーや結合に使った列のように、相手がいれば必ず値が入っている列を選びます。

左辺が NULL の行も、IN と NOT IN のどちらにも入らない

逆向きの例も見ておきます。注文を起点に、「東京の顧客の注文」と「大阪の顧客ではない注文」を出します。

東京の顧客の注文(IN)
SELECT id, customer_id
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE city = '東京')
ORDER BY id;
idcustomer_id
1011
1021
大阪の顧客ではない注文(NOT IN)
SELECT id, customer_id
FROM orders
WHERE customer_id NOT IN (SELECT id FROM customers WHERE city = '大阪')
ORDER BY id;
idcustomer_id
1011
1021

ゲスト購入の 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;
idcustomer_idamount
10213000
10422500

外側の注文 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_totalmax_total
3750.04200

顧客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;
nametotal
顧客A4200
顧客B3300

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)と比べてみます。

顧客B のどれかの注文より高い注文(ANY、PostgreSQL)
SELECT id, amount
FROM orders
WHERE amount > ANY (SELECT amount FROM orders WHERE customer_id = 2)
ORDER BY id;
idamount
1011200
1023000
1042500
1051500

> ANY は「一覧のどれか1つより大きい」、つまり最小値の 800 より大きい注文です。

顧客B のすべての注文より高い注文(ALL、PostgreSQL)
SELECT id, amount
FROM orders
WHERE amount > ALL (SELECT amount FROM orders WHERE customer_id = 2)
ORDER BY id;
idamount
1023000

> 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 の注文額と比べてみます。

空の一覧に対する ALL(PostgreSQL)
SELECT id, amount
FROM orders
WHERE amount > ALL (SELECT amount FROM orders WHERE customer_id = 3)
ORDER BY id;
idamount
1011200
1023000
103800
1042500
1051500

全件が返りました。PostgreSQL のドキュメントは、ALL はすべての行で真なら真(サブクエリが行を返さない場合を含む)と定めています。「比べる相手が1つもない」ので、反例も存在しない、という扱いです。

同じことを MAX で書くと、結果が逆になります。

空の一覧の 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 を参照するとエラーになります。

LATERAL なしで外側を参照する(エラー)
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;
エラー(PostgreSQL 18.6)
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 の付いたサブクエリは、参照している列を提供する行ごとに評価され、その結果の行が元の行と結合されます。

顧客ごとの最新の注文1件(CROSS JOIN LATERAL、PostgreSQL)
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;
idnameorder_idamountordered_on
1顧客A10230002026-11-05
2顧客B10425002026-11-10

スカラーサブクエリでは1列しか取れませんでしたが、LATERAL なら複数の列も複数の行も取れます。LIMIT 3 にすれば「顧客ごとの直近3件」になります。

LEFT JOIN LATERAL ... ON true で相手のない行も残す

CROSS JOIN LATERAL は、サブクエリが1行も返さない顧客(C と D)を落とします。残したい場合は LEFT JOIN LATERAL にして、結合条件に ON true を書きます。

注文のない顧客も残す(LEFT JOIN LATERAL、PostgreSQL)
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;
idnameorder_idamountordered_on
1顧客A10230002026-11-05
2顧客B10425002026-11-10
3顧客CNULLNULLNULL
4顧客DNULLNULLNULL

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() を付けてから絞る形で書けます。

顧客ごとの最新の注文1件(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;
idnameorder_idamountordered_on
1顧客A10230002026-11-05
2顧客B10425002026-11-10
3顧客CNULLNULLNULL
4顧客DNULLNULLNULL

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 は、派生テーブルに名前を付けてクエリの先頭へ出したものと考えられます。たとえば前の節の「顧客ごとの合計を顧客名と結合する」クエリは、次のように書いても同じ結果です。

派生テーブルを 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 することはできないとして、次の形を挙げています。

MySQL で制限される形(マニュアルの例)
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 になる

IN と EXISTS の実行計画
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);
実行計画(PostgreSQL 18.6、どちらも同じ形)
 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 になる

NOT EXISTS の実行計画
EXPLAIN (COSTS OFF)
SELECT * FROM big_customers c
WHERE NOT EXISTS (SELECT 1 FROM big_orders o WHERE o.customer_id = c.id);
実行計画(PostgreSQL 18.6)
 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 はアンチジョインにならない

NOT IN の実行計画
EXPLAIN (COSTS OFF)
SELECT * FROM big_customers c
WHERE c.id NOT IN (SELECT customer_id FROM big_orders);
実行計画(PostgreSQL 18.6)
 Seq Scan on big_customers c
   Filter: (NOT (ANY (id = (hashed SubPlan 1).col1)))
   SubPlan 1
     ->  Seq Scan on big_orders

NOT IN は結合に変換されず、サブクエリの結果をハッシュ表にして(hashed SubPlan)、顧客の各行をフィルタで判定しています。NULL があると結果が UNKNOWN になるという NOT IN の意味を、アンチジョインでは表せないためです。サブクエリに WHERE customer_id IS NOT NULL を付けても、手元の 18.6 では同じ hashed SubPlan のままでした。

さらに、work_mem を 64kB に下げてサブクエリの結果がハッシュ表に収まらないようにすると、計画は次のように変わりました。

work_mem = 64kB のときの NOT IN(PostgreSQL 18.6)
 Seq Scan on big_customers c
   Filter: (NOT (ANY (id = (SubPlan 1).col1)))
   SubPlan 1
     ->  Materialize
           ->  Seq Scan on big_orders

hashed が消え、顧客1行ごとにマテリアライズした 20 万行を順に調べる形です。行数が増えるほど急激に遅くなる計画です。参考までに、通常の設定で1回ずつ実行した時間は、NOT EXISTS が約 10ms、IS NOT NULL 付きの NOT IN が約 59ms でした(手元のノート PC で1回ずつ測っただけの値で、ベンチマークではありません)。正しさの面でも速さの面でも、否定は NOT EXISTS で書くのが基本です。

SELECT リストの相関サブクエリは行ごとの SubPlan になる

SELECT リストの相関サブクエリと、JOIN + GROUP BY
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;
実行計画(PostgreSQL 18.6、相関サブクエリ)
 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)
実行計画(PostgreSQL 18.6、LEFT JOIN + GROUP BY)
 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 c

SELECT リストに書いた相関サブクエリは、顧客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 pulloutEXPLAIN の後の SHOW WARNINGS で、サブクエリが消えて外側の結合に統合されたクエリが見える
Duplicate WeedoutExtra 列に Start temporary と End temporary
FirstMatchExtra 列に FirstMatch(tbl_name)
LooseScanExtra 列に LooseScan(m..n)
Materializationselect_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 で確かめると次のようになりました。

NOT EXISTS の EXPLAIN QUERY PLAN(SQLite 3.54.0)
QUERY PLAN
|--SCAN c
`--CORRELATED SCALAR SUBQUERY 1
   `--SEARCH o USING COVERING INDEX orders_customer_id (customer_id=?)
IN の EXPLAIN QUERY PLAN(SQLite 3.54.0)
QUERY PLAN
|--SEARCH customers USING INTEGER PRIMARY KEY (rowid=?)
`--LIST SUBQUERY 1
   `--SCAN orders USING COVERING INDEX orders_customer_id

NOT EXISTS は CORRELATED SCALAR SUBQUERY として外側の行ごとにインデックスを引く形、相関しない IN は LIST SUBQUERY としてサブクエリの一覧を先に作り、主キーで顧客を引く形です。SQLite では、相関サブクエリの中の検索条件にインデックスがあるかどうかが特に効きます。インデックスの効く条件・効かない条件は データベースインデックス入門 で扱っています。

よくあるエラーと対処

症状・エラーPostgreSQL 18.6SQLite 3.54.0MySQL 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 columnsub-select returns 2 columns - expected 1ERROR 1241 Operand should contain 1 column(s)列を1つにするか LATERAL
派生テーブルから外側の列を参照invalid reference to FROM-clause entry(LATERAL を付けるよう HINT が出る)no such columnERROR 1054 Unknown columnLATERAL を付ける
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 の右側に何が並んでいるかが見えれば、多くの問題はそこで原因がわかります。

参考リンク

関連記事

SQL の UPSERT - ON CONFLICT・ON DUPLICATE KEY UPDATE・MERGE の書き方と同時実行の注意

SQL の UPSERT - ON CONFLICT・ON DUPLICATE KEY UPDATE・MERGE の書き方と同時実行の注意

約73分

「あれば更新、なければ挿入」を SQL で書く UPSERT を、同じ小さなサンプルデータに対する実行結果を並べて整理します。SELECT してから INSERT・UPDATE する書き方が同時実行で壊れる理由とユニーク制約が必要な理由、PostgreSQL の INSERT ... ON CONFLICT(DO NOTHING・DO UPDATE・EXCLUDED・ON CONSTRAINT・WHERE 付きの更新・部分インデックス・RETURNING と PostgreSQL 18 の OLD / NEW)、PostgreSQL 15 の MERGE と 17 で入った RETURNING・merge_action()・WHEN NOT MATCHED BY SOURCE、MySQL の INSERT ... ON DUPLICATE KEY UPDATE(非推奨の VALUES() と行エイリアス AS new、affected rows が 1・2・0 になる挙動、代入の順番、複数のユニークキーで別の行が更新される問題、AUTO_INCREMENT の欠番)、REPLACE と INSERT IGNORE の危険性、SQLite の ON CONFLICT と INSERT OR REPLACE の違い、SQL Server の MERGE と HOLDLOCK、2つのセッションを同時に動かしたときの挙動、1文の中で同じキーを2回更新したときのエラー、NULL がユニーク制約で重複扱いにならない問題と NULLS NOT DISTINCT、DO NOTHING で既存の行が返らない問題と回避策、一括 UPSERT、よくあるエラーまでを、PostgreSQL 18.6・MySQL 8.4.13・SQLite 3.54.0 の実行結果と各公式ドキュメントをもとにまとめます。

SQL の CASE 式の使い方 - 検索 CASE と単純 CASE、条件付き集計・並べ替え・UPDATE と評価順序の注意

SQL の CASE 式の使い方 - 検索 CASE と単純 CASE、条件付き集計・並べ替え・UPDATE と評価順序の注意

約68分

SQL の CASE 式を、同じ小さなサンプルデータに対する実行結果を並べて整理します。単純 CASE と検索 CASE、ELSE を省略すると NULL になること、上から順に評価して最初に一致した分岐を返すこと、WHEN NULL が一致しない理由、SELECT・WHERE・GROUP BY・HAVING・ORDER BY・UPDATE など書ける場所、年齢層・金額帯の区分け、SUM(CASE ...)・COUNT(CASE ...) の条件付き集計とピボット、FILTER 句との比較、任意の並び順、UPDATE で複数の値を1文で書き換える方法と ELSE 漏れの事故、NULLIF による 0 除算の回避、COALESCE・GREATEST・LEAST・MySQL の IF()・SQLite の iif() との関係、結果の型の決まり方とエラー、短絡評価が効かない場面(定数式の早期評価と集約関数)、WHERE の CASE とインデックス、ストアドプロシージャの CASE 文との違いまでを、PostgreSQL 18.6・MySQL 8.4.13・SQLite 3.54.0 の実行結果と各公式ドキュメントをもとにまとめます。

SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序

SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序

約54分

SQL の GROUP BY と HAVING を、同じ小さなサンプルデータに対する実行結果を並べて整理します。COUNT(*) と COUNT(列) と COUNT(DISTINCT 列) の違い、SUM・AVG・MIN・MAX と NULL、WHERE と HAVING の違いと FROM・WHERE・GROUP BY・HAVING・SELECT・ORDER BY の論理的な処理順序、SELECT の別名を GROUP BY や HAVING で使えるかの製品差、PostgreSQL の関数従属と MySQL の ONLY_FULL_GROUP_BY、SQLite の bare column、LEFT JOIN で 0 件のグループを出す方法、FILTER 句と CASE 式による条件付き集計、ROLLUP・GROUPING SETS・CUBE と MySQL の WITH ROLLUP、ウィンドウ関数との違い、よくあるエラー、WHERE で先に絞る理由とインデックスまでを、各公式ドキュメントを一次ソースにまとめます。

楽天市場のおすすめPR / 楽天アフィリエイト