回答。

歴史的背景:

ネストされたサブクエリは、SQLの表現力を強化し、単純なSELECT文に収まらない複雑なビジネス要件を解決するために、当初設計されました。しかし、データ量の増加とリレーショナルモデルの複雑さの進展に伴い、ネストされたサブクエリが常に効率的に動作するわけではないことが理解されました。これは、特定のDBMSのオプティマイザーの実装に多く依存しています。

問題:

主な課題は、可読性、ロジックの正確さ、およびパフォーマンスの間でのトレードオフを見つけることです。ネストされたサブクエリが常にJOIN操作に最適化されるわけではなく、しばしば高コストのネステッドループに変わることが多いです。

解決策:

集約関数や複雑な式の計算に必要な場合は、SELECTでネストされたサブクエリを使用します。
大量のデータについては、JOINに切り替えることが推奨されます。これにより、オプティマイザーがクエリをセットベースにし、インデックスを適用できるようになります。
可読性やパフォーマンスを向上させるために、サブクエリを外部に移動（WITH/CTEを使用）します。

コード例:

-- SELECT内のネストされたサブクエリ（注意）
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.client_id = clients.id) AS order_count
FROM clients;

-- JOINによる同等のクエリ（通常は速い）:
SELECT clients.name, COUNT(orders.id) AS order_count
FROM clients
LEFT JOIN orders ON orders.client_id = clients.id
GROUP BY clients.name;

主な特徴:

ネストされた相関サブクエリは、しばしばO(N*M)のパフォーマンスに繋がります。
非相関サブクエリは、安全で、より高速です。
サブクエリをWITH/CTEまたはJOINに移動することで、プランの予測可能性が向上し、実行時間が短縮されます。

カモフラージュされた質問。

SELECT内のネストされたサブクエリは、同様のLEFT JOINよりも速く実行されますか？

ほとんどの場合—いいえです。SELECT内の相関サブクエリは、外部クエリの各行に対して実行されますが、JOINはテーブル全体のインデックスを使用して一度だけ構築されます。

CTE（WITH）の代わりにFROM内でサブクエリを使用しても良いか、違いはありますか？

はい、FROM内のサブクエリは次の通りです:

SELECT t1.id, sub.agg
FROM table1 t1
JOIN (SELECT id, MAX(val) AS agg FROM table2 GROUP BY id) sub ON t1.id = sub.id;

しかし、CTEは時に可読性が高く、実行プランの最適化において異なる結果をもたらす場合があります。

すべてのネストされたサブクエリは、類似のJOINに最適化されますか？

いいえ。すべてのDBMSがこれを同じように実行できるわけではなく、ネストされたサブクエリは、特に外部クエリと内部クエリに相関がある場合、各行をスキャンすることにつながり得ます。

一般的な間違いとアンチパターン

大量のデータに対してSELECT内で相関サブクエリを使用すること。
内部と外部のクエリでフィルタ条件を重複して記載すること。
問題の分析なしに、すべてのネストされたサブクエリをJOINに置き換えようとすること。

実生活の例

ネガティブケース

営業マネージャーが、内部SELECTを使って顧客ごとの注文数を計算してレポートを作成しました。実行時間は数分、サーバーの負荷は幾何級数的に増加しました。

利点:

明確なロジック 欠点:
多数の顧客に対して非常に遅いレポート
サーバーの非効率な負荷

ポジティブケース

クエリをLEFT JOINとグルーピングに書き換えました。

利点:

数秒での実行
インデックスの使用 欠点:
より複雑なGROUP BYとLEFT JOINで、データ構造の理解が必要