はじめに

サブクエリは別のクエリの中にネストされたSQLクエリです。関数が別の関数を呼び出せるように、SELECT の中に別の SELECT を含めることができ、内部クエリの結果を外部クエリがフィルタリング、計算、最終結果の構成に使用します。

サブクエリは SELECT、FROM、WHERE/HAVING の3つの場所に現れることがあります。サブクエリが返す結果の種類によって動作が変わり、Scalar、Column、Row、Table という分類がここで意味を持ちます。

例では以下を使用します。

clientes: id, nome, cidade
pedidos:  id, cliente_id, valor, criado_em
produtos: id, nome, preco, categoria_id
categorias: id, nome

Enter fullscreen mode Exit fullscreen mode

結果による種類

ちょうど1つの値を返します。1行1列です。単一の値が期待される場所であればどこでも使用できます — SELECT、WHERE、または HAVING など。

Scalar Subquery 

-- 最大の注文をした顧客は誰か？
SELECT nome
FROM clientes
WHERE id = (
  SELECT cliente_id
  FROM pedidos
  ORDER BY valor DESC
  LIMIT 1
);

Enter fullscreen mode Exit fullscreen mode

内部のサブクエリは1つの cliente_id を返します — 最も高額な注文の顧客IDです。外部クエリはその値を使って名前を取得します。

Scalar subquery は SELECT の中でも計算列として直接機能します。

SELECT
  nome,
  (SELECT COUNT(*)FROM pedidosWHERE cliente_id= c.id)AS total_pedidos
FROM clientes c;

Enter fullscreen mode Exit fullscreen mode

Scalar subquery が複数行を返すとデータベースはエラーを発生させます。そのため、LIMIT 1 や集約関数を使って1行に限定するのが一般的です。

Column Subquery

単一列の複数行を返します — 基本的には値のリストです。主に IN、NOT IN、ANY、ALL 演算子と共に使用されます。

-- 1000を超える注文を少なくとも1件行った顧客
SELECT nome
FROM clientes
WHERE id IN (
  SELECT DISTINCT cliente_id
  FROM pedidos
  WHERE valor > 1000
);

Enter fullscreen mode Exit fullscreen mode

サブクエリは対象となる顧客IDの列を返します。外部クエリの IN は各 id がこのリストに含まれているかを確認します。

NOT IN を使った逆のケースも同様に便利ですが、注意が必要です。サブクエリが NULL を返す場合、NOT IN は直感に反した動作をし、行を返さないことがあります。この場合は NOT EXISTS を優先してください。

ANY と ALL は特定のシナリオでより表現力のある代替手段です。

-- カテゴリ2のいずれかの製品より高価な製品
SELECT nome, preco
FROM produtos
WHERE preco > ANY (
  SELECT preco FROM produtos WHERE categoria_id= 2
);

-- カテゴリ2のすべての製品より高価な製品
SELECT nome, preco
FROM produtos
WHERE preco > ALL (
  SELECT preco FROM produtos WHERE categoria_id= 2
);

Enter fullscreen mode Exit fullscreen mode

Row Subquery

複数の列を持つ1行を返します。あまり一般的ではありませんが、値のセットを一度に比較するのに役立ちます。

-- 名前と価格が完全に一致する製品を見つける
SELECT id
FROM produtos
WHERE (nome, preco)= (
  SELECT nome, preco FROM produtos WHERE id = 10
);

Enter fullscreen mode Exit fullscreen mode

行全体の比較により、複数の AND 条件を書く必要がなくなります。サポート状況はデータベースによって異なります — PostgreSQL と MySQL は良好にサポートしていますが、SQL Server はサポートが限定的です。

Table Subquery

複数行・複数列を返します — 一時テーブルとして扱われる完全なデータセットです。derived table や inline view とも呼ばれます。FROM 句に現れ、エイリアスが必要です。

-- 500を超えた顧客のみの売上を集計
SELECT cliente_nome, receita_total
FROM (
  SELECT
    c.nomeAS cliente_nome,
    SUM(p.valor) AS receita_total
  FROM clientes c
  JOIN pedidos p ON p.cliente_id= c.id
  GROUP BY c.nome
) AS resumo
WHERE receita_total> 500;

Enter fullscreen mode Exit fullscreen mode

内部のサブクエリは顧客ごとの売上を計算します。外部クエリはその結果をテーブルとして扱い、フィルタを適用します。これは WHERE が GROUP BY より前に評価されるため直接は不可能ですが、table subquery によって回避できます。

動作による種類

Nested Subquery (独立)

サブクエリが外部クエリから完全に独立して実行可能な場合を nested と呼びます。外部テーブルの列を参照せず、一度だけ評価され、その結果が外部クエリに渡されます。

SELECT nome
FROM clientes
WHERE cidade= (
  SELECT cidade FROM clientes WHERE id= 1
);

Enter fullscreen mode Exit fullscreen mode

内部のサブクエリは顧客1の都市を取得します。これは外部クエリのデータに依存せず、一度実行されて 'São Paulo' を返し、外部クエリはその値を使って全顧客をフィルタリングします。

独立しているため、nested subquery は一般的に効率的です — データベースは一度だけ実行し、その結果を再利用します。

Correlated Subquery (依存)

サブクエリが外部クエリの列を参照する場合を correlated と呼びます。これは外部クエリが処理する各行に対してサブクエリが再評価されることを意味します — 大きなテーブルではコストがかかる可能性がありますが、他の方法では不可能なクエリを表現できます。

-- 注文合計が全体平均を上回る顧客
SELECT nome
FROM clientes c
WHERE (
  SELECT SUM(valor)
  FROM pedidos p
  WHERE p.cliente_id=  c.id-- 外部クエリへの参照
)> (
  SELECT AVG(valor) FROM pedidos
);

Enter fullscreen mode Exit fullscreen mode

サブクエリの WHERE p.cliente_id = c.id は外部クエリから来る c.id を参照しています。外部クエリが調べる各顧客に対して、その顧客の id を使ってサブクエリが再実行されます。

correlated subquery の最も古典的な用途は EXISTS と NOT EXISTS です。

-- 少なくとも1件の注文をした顧客
SELECT nome
FROM clientes c
WHERE EXISTS (
  SELECT 1
  FROM pedidos p
  WHERE p.cliente_id= c.id
);

-- 一度も注文をしたことがない顧客
SELECT nome
FROM clientes c
WHERE NOT EXISTS (
  SELECT 1
  FROM pedidos p
  WHERE p.cliente_id = c.id
);

Enter fullscreen mode Exit fullscreen mode

EXISTS は最初の一致を見つけた時点で真を返します — 件数を数えたりデータを取得したりする必要はなく、少なくとも1行が存在することを確認するだけです。そのため NULL を含む可能性がある場合など、多くのケースで IN より効率的です。

Subquery vs JOIN

多くのサブクエリは JOIN に書き換えられ、その逆も可能です。どちらを選ぶかは可読性と、時にはパフォーマンスによって決まります — ただし、現代のオプティマイザは両者で同一の実行計画を生成することがよくあります。

-- サブクエリの場合
SELECT nome FROM clientes
WHERE id IN (SELECT cliente_id FROM pedidos WHERE valor > 1000);

-- JOIN の場合
SELECT DISTINCT c.nome
FROM clientes c
INNER JOIN pedidos p ON p.cliente_id = c.id
WHERE p.valor > 1000;

Enter fullscreen mode Exit fullscreen mode

結果は同じです。サブクエリは自然言語で質問を考える方法に近く、JOIN はテーブル間の関係を明示します。意図がより明確になる方を使用してください — また、サブクエリが NULL を含む可能性がある場合や多くの行を返す場合は IN より EXISTS を優先してください。