【SQL】相関サブクエリ / 自己相関サブクエリ

SQL

 ■ はじめに

https://gihyo.jp/dev/serial/01/sql_academy2/000901

などで、 「SQLでループ→ 相関サブクエリ」とあるので、
相関サブクエリについて、学ぶ。

目次

【１】相関サブクエリ / 自己相関サブクエリ
【２】相関サブクエリの捉え方
【３】使用上の注意

 【１】相関サブクエリ / 自己相関サブクエリ

https://dk521123.hatenablog.com/entry/2015/12/21/002727

で、「サブクエリ」を扱ったが...

 相関サブクエリ（co-related subquery）とは... 

* 内部クエリから外部クエリの FROM 句のテーブルを参照するサブクエリ

 自己相関サブクエリとは... 

* 自己結合と組み合わせた相関サブクエリ

 補足：サブクエリとの違い

* 相関サブクエリは、内部クエリと外部クエリで、
　単独のSQL文としては実行できない

下記【サンプル】の例2より 

・・・略・・・
WHERE
  ・・・略・・・
  FROM Employees E2 WHERE E1.departmentId = E2.departmentId
 -- 2つのテーブルにまたがっている

 【２】相関サブクエリの捉え方

http://gihyo.jp/dev/serial/01/sql_academy2/000901
http://gihyo.jp/dev/serial/01/sql_academy2/000902

より
 * 相関サブクエリには2つの側面がある

１）手続き言語的な観点から見た場合、
　　相関サブクエリの持つ役割は、『ループ隠し』
 => サブクエリの外側のテーブル1行ごとに対して、
　　内側のサブクエリが実行される

２）集合指向言語的な観点から見た場合、
　『集合のカット（分割）』

http://language-and-engineering.hatenablog.jp/entry/20101108/p1

より
 * サブクエリを『関数』と考えてみよう

  (1) 引数　：外部クエリから内部クエリへのパラメータ
  (2) 戻り値：内部クエリの出力
  (3-1) 非相関サブクエリの場合は、一度実行すればよい
  (3-2) 相関サブクエリの場合は、毎回実行する必要がある

 【３】使用上の注意

１） 正規形違反の返却結果
２） パフォーマンス問題

http://gihyo.jp/dev/serial/01/sql_academy2/000904

より

 １）正規形違反の返却結果

 * 更新やスカラサブクエリと組み合わせるときは、
　結果が第1正規形を満たすか、厳重にチェックしないと危険
  => 常に単一行に制限する安全な方法は、
　　「集約した結果のみを返す」か
 　   「サブクエリ内の結合条件で一意キーを指定する」こと

 ２）パフォーマンス問題

 * 単純なサブクエリに比べて、相関サブクエリの実行コストは相当高い
  => 軽減するための方法として有効なことは、
     サブクエリ内の結合キーにインデックスを作成する
　（あるいは主キーを結合キーとして使う）

後日談 

 * 実際に、50万レコードほどのテーブルで相関サブクエリを使った結果、
　数十分掛かってしまった
  => インデックス追加およびSQL文変更で解決できた

 【４】サンプル

 * 以下の関連記事を参照

グループ内のMAXデータを取得する
https://dk521123.hatenablog.com/entry/2018/10/06/230841
SQLで変化（増加/変化なし/減少）について考える
https://dk521123.hatenablog.com/entry/2021/08/26/222043

 参考文献

http://codezine.jp/article/detail/907

以下のシリーズが分かりやすい

http://gihyo.jp/dev/serial/01/sql_academy2/000901
http://gihyo.jp/dev/serial/01/sql_academy2/000902
http://gihyo.jp/dev/serial/01/sql_academy2/000903
http://gihyo.jp/dev/serial/01/sql_academy2/000904

 関連記事

サブクエリ / 副問合せ
https://dk521123.hatenablog.com/entry/2015/12/21/002727
グループ内のMAXデータを取得する
https://dk521123.hatenablog.com/entry/2018/10/06/230841
SQLで変化（増加/変化なし/減少）について考える
https://dk521123.hatenablog.com/entry/2021/08/26/222043