SQLのサブクエリ（副問い合わせ）とは ― IN・EXISTS

SQLのサブクエリ（副問い合わせ）とは、SELECT文の中に丸括弧で囲んで書く、別のSELECT文です。外側のSELECT文はメインクエリと呼びます。

「全注文の平均より高い注文だけ」のように、別の問い合わせで求めた値で絞りたいときに使います。

例には、次の顧客テーブルと注文テーブルを使います。注文5006は会員登録をしていない人の注文で、顧客IDがNULLです。

全注文の平均より高い注文だけを知りたいとします。GROUP BYの記事の集約関数で平均は求められますが、WHEREには集約関数を直接書けません。そこで、平均を求めるSELECT文を、WHEREの条件の中に入れます。

SELECT 注文ID, 金額
FROM 注文
WHERE 金額 > (
  SELECT AVG(金額) FROM 注文
);

考え方のうえでは、内側のSELECT文が先に実行され、平均の25200が外側に渡ります。外側は「金額 > 25200」の条件で行を絞り、注文5001と注文5006が残ります。実際の実行手順はDBMSが決めます。

サブクエリは、WHERE句のほか、FROM句とSELECT句にも書けます。SELECT文の基本のWHERE句に、別のSELECT文を入れられると考えてください。

WHERE句のサブクエリは、内側が返す形によって、書き方が決まります。比較演算子は1つの値、INは1列の複数行を受け取り、EXISTSは行があるかどうかだけを見ます。

比較演算子では、内側は1つの値を返す

内側が1行1列だけを返すサブクエリを、スカラーサブクエリと呼びます。前の節の平均を求める例が、これに当たります。

内側が2行以上を返すと、PostgreSQLでもMySQLでもエラーになります。内側が0行のときはエラーにならず、結果はNULLになります。NULLとの比較は成り立たないため、外側の行は1行も残りません。

INでは、内側が返した複数の値のどれかに等しいかを調べる

INは、外側の値が内側の結果のどれかに等しいときに真になります。内側は1列を返す必要があります。

SELECT 氏名
FROM 顧客
WHERE 顧客ID IN (
  SELECT 顧客ID FROM 注文
);

内側は、注文テーブルの顧客IDを6行返します。田中さんの顧客IDは内側に2回出ますが、外側では顧客テーブルの1行として1回だけ残ります。結果は、田中さん、佐藤さん、鈴木さんの3行です。

NOT INは、INの逆に、どの値にも等しくない行を残します。ただし、内側の結果にNULLが1つでも入っていると、NOT INはどの行でも真になりません。注文5006の顧客IDがNULLなので、注文のない顧客を探す次の文は、0行を返します。

SELECT 氏名
FROM 顧客
WHERE 顧客ID NOT IN (
  SELECT 顧客ID FROM 注文
);

PostgreSQLの公式ドキュメントも、内側にNULLがあるとNOT INの結果が真ではなくNULLになると説明しています。内側に「WHERE 顧客ID IS NOT NULL」を足すか、次に説明するNOT EXISTSを使うと、高橋さんが正しく残ります。値を並べたNOT INの一覧にNULLを書いても同じ結果になることは、INとBETWEENの記事で確かめています。

EXISTSでは、内側が1行でも返すかを調べる

EXISTSは、内側が1行以上を返すときに真になり、0行のときに偽になります。返された値の中身は見ないため、内側のSELECT句は「SELECT 1」のように短く書くのが一般的です。ただし、COUNT(*)などの集約関数を書くと、条件に合う行が無くても内側は1行を返します。そのため、EXISTSはどの行でも真になります。

SELECT 氏名
FROM 顧客 AS c
WHERE EXISTS (
  SELECT 1 FROM 注文 AS o
  WHERE o.顧客ID = c.顧客ID
);

この文の内側は、外側の列c.顧客IDを使っています。このようなサブクエリを、次の節で説明する相関サブクエリと呼びます。NOT EXISTSにすると、注文が1件も無い顧客だけが残ります。

相関サブクエリとは、外側のSELECT文の列を使うサブクエリです。外側の1行を取り出すたびに、その行の値を使って内側が実行されます。

外側の列は、「表名.列名」や「別名.列名」の形で指します。先ほどの例のcとoが、表に付けた別名です。内側と外側に同じ列名があっても、c.顧客ID、o.顧客IDと書き分けられます。

先ほどのEXISTSの例で、顧客101の行を取り出したとします。内側は「o.顧客ID = 101」の注文を探して2行を返すので、EXISTSは真になります。顧客104では内側が0行になるため、EXISTSは偽です。

この1行ごとの手順は、考え方のうえでの流れです。MySQLのように、相関サブクエリを結合を使う形に書き換えて実行するDBMSもあります。

FROM句に書いたサブクエリの結果は、1つの表として扱われます。この表を派生表と呼びます。

SELECT t.顧客ID, t.合計
FROM (
  SELECT 顧客ID, SUM(金額) AS 合計
  FROM 注文
  GROUP BY 顧客ID
) AS t
WHERE t.合計 >= 50000;

内側が顧客ごとの合計を求め、外側はその結果を表tとして、合計が50000以上の行を残します。この例のように絞るだけなら、HAVINGでも書けます。FROM句のサブクエリを使うと、集計した結果に、別の条件や結合を重ねられます。UNION ALLでつないだ結果を合計するときも、この形で書きます。

FROM句のサブクエリには、別名を付けます。標準SQLでは別名が必須で、MySQLも必須です。PostgreSQLは省略できますが、ほかのDBMSでも動くように付けるのがよいとしています。

SELECT句にサブクエリを書くと、その行の列として値が並びます。内側は1つの値を返す必要があります。

SELECT 氏名,
  (SELECT COUNT(*) FROM 注文 AS o
   WHERE o.顧客ID = c.顧客ID) AS 注文数
FROM 顧客 AS c;

この例も相関サブクエリです。注文がない高橋さんも、COUNT(*)が0を返すため、0件の行として結果に残ります。

注文のある顧客の氏名を求めるだけなら、JOINでもIN・EXISTSでも書けます。違いは、結果の行数です。

SELECT 顧客.氏名
FROM 顧客
INNER JOIN 注文
  ON 顧客.顧客ID = 注文.顧客ID;

このJOINは5行を返し、田中さんと佐藤さんが2行ずつ出ます。INやEXISTSで書くと、前の節のとおり3行になります。JOINで1人1行にするには、DISTINCTが必要です。

注文のない顧客は、LEFT JOINで注文側がNULLの行を探す書き方でも求められます。どの書き方が速いかは、DBMSとデータの量によって変わります。上の表で比べているのは、結果の違いだけです。

次の図でサブクエリの書き方を選ぶと、SQL文、内側の結果、全体の結果が変わります。顧客テーブルと注文テーブルは、最初に示したものと同じです。図は実際のデータベースには接続しておらず、結果はあらかじめ確かめた固定の値です。

SELECT 注文ID, 金額
FROM 注文
WHERE 金額 > (
  SELECT AVG(金額) FROM 注文
);

SELECT 氏名
FROM 顧客
WHERE 顧客ID IN (
  SELECT 顧客ID FROM 注文
);

SELECT 氏名
FROM 顧客
WHERE 顧客ID NOT IN (
  SELECT 顧客ID FROM 注文
);

SELECT 氏名
FROM 顧客
WHERE 顧客ID NOT IN (
  SELECT 顧客ID FROM 注文
  WHERE 顧客ID IS NOT NULL
);

SELECT 氏名
FROM 顧客 AS c
WHERE EXISTS (
  SELECT 1 FROM 注文 AS o
  WHERE o.顧客ID = c.顧客ID
);

SELECT 氏名
FROM 顧客 AS c
WHERE NOT EXISTS (
  SELECT 1 FROM 注文 AS o
  WHERE o.顧客ID = c.顧客ID
);

SELECT t.顧客ID, t.合計
FROM (
  SELECT 顧客ID, SUM(金額) AS 合計
  FROM 注文
  GROUP BY 顧客ID
) AS t
WHERE t.合計 >= 50000;

SELECT 氏名,
  (SELECT COUNT(*)
   FROM 注文 AS o
   WHERE o.顧客ID = c.顧客ID)
  AS 注文数
FROM 顧客 AS c;

SELECT 注文ID, 顧客ID, 金額
FROM 注文 AS o
WHERE 金額 = (
  SELECT MAX(金額) FROM 注文
  WHERE 顧客ID = o.顧客ID
);

• PostgreSQL公式ドキュメント「Value Expressions」（スカラーサブクエリ、外側の列を参照できること）
• PostgreSQL公式ドキュメント「Subquery Expressions」（EXISTS・IN・NOT INとNULL）
• PostgreSQL公式ドキュメント「Table Expressions」（FROM句のサブクエリと別名）
• MySQL 8.4リファレンスマニュアル「Subqueries」（相関サブクエリとその書き換え・派生表・複数行を返すエラー）