スポンサーリンク

「相関サブクエリ」とは何かを理解して，複雑なSQLでも読めるようになろう

DB PostgreSQL SQL

SQLの「相関サブクエリ」がわかれば・・・

  • 巨大なSQLが，迷わずに読めるようになる。
  • 「関数」のような，便利なサブクエリを書けるようになる。


以下では，

  • 「相関サブクエリ」とは何か？
  • 普通のサブクエリ（非相関サブクエリ）やJOIN操作とは何が違うのか？
  • 多重にネストされた，巨大なSQLの読み方は？

という点を論じる。

サンプルデータ，および全体の方針

まず，相関サブクエリの説明のために，以下のようなテーブルを例として取り上げる。

  • table1が，普通のデータ
  • table2が，マスタデータ（ホワイトリスト）

「マスタに一致しないレコードをはじく」という操作をしたい。


方法は３パターンある。

  • サブクエリ無し　…（１）
  • サブクエリ有り
    • INで非相関サブクエリ　　…（２）
    • EXISTSで相関サブクエリ　…（３）

（１）サブクエリ無しでJOIN

JOINの意味やイメージを復習する。

SELECT
	table1.fuga
FROM
	table2 LEFT JOIN table1
	  ON
	table2.hoge = table1.hoge
;

table2優位でJOINする。

table2というマスタ（ホワイトリスト）が許可するものだけ，table1の中身は生き残る。

table1に対する，一種のバリデーション（またはフィルタ）。



表と表が左右から衝突して，不適切なレコードが右側からいっぺんに抜け落ちるようなイメージ。

優位な左側の表が「固い」表で，右側の表はその固さに負ける。

table1が右側からtable2に向かって「ぶつかってくる」のだが，その際，table2に一致しない物ははじかれる。



JOINとは「情報付加」である，とみなせる。

ここでは，table2というホワイトリストに対して，右側からtable1内の情報を付加している。

中心はあくまでホワイトリストである点に注意。



（２）INで非相関サブクエリ

サブクエリを使って，同様の操作をしてみる。

これは相関サブクエリではなく，普通のサブクエリ（非相関サブクエリ）である。

SELECT
	table1.fuga
FROM
	table1
WHERE
	table1.hoge IN (
		SELECT
			table2.hoge
		FROM
			table2
	)
;

table2というホワイトリストを，まず１つの表として確立する。（サブクエリ内。）

その完成済みのホワイトリストに照らし合わせながらtable1を１件ずつスキャンして，生き残るかどうかを順に判定してゆく。

table1のうち，ホワイトリストに載っていないものは捨てられる。


（１）では左右からテーブルが衝突して，いっぺんに無効データが抜け落ちるような劇的なイメージだった。

（２）は（１）と違って，table1の各レコードの可否をきまじめに１件１件，ホワイトリストtable2を見ながらチェックしているイメージ。



全般的にサブクエリは，最初，table2に関する「話題を確立」してから，まるごとtable1に「変換」しているようなイメージ。

いわばテーブル変換。

話題の中心がtable2テーブルからtable1テーブルにかわる。



だから，サブクエリがある場合は

  • まず，サブクエリの中身を読んでから
  • 次に，その外側を読む

というふうに読み進めていけば，長いSQLであっても理解できる。

原則的に，内側から外側に向かって情報が流れてゆく，ということだ。


※ただし（３）で後述するとおり，相関サブクエリの場合には，こうはいかなくなる。




※なお，もし，NOT INで副表内にNULLが入っていると，思い通りの結果が得られない。「NULLではない物」が生き残る，のではない。

http://www.geocities.jp/mickindex/dat...
NOT IN のサブクエリで使用されるテーブルのキーに NULL が含まれている場合、結果は必ず空になるのです。

（２）の補足：サブクエリを「関数」と考えてみよう

上のSQLの table1.hoge IN （サブクエリ） の部分には，「マスタレコードを返す関数」のようなものが存在する，と考えるとわかりやすい。



( サブクエリ )　が表すもの：

  • 引数：なし。
  • 返り値：table2の全レコード。


という関数。


引数が無いので，メインクエリに関係なく，いつも同じ結果を返す。

だから，１回だけ実行すればよい。


table1.hoge IN ( サブクエリ )　が表すもの：

  • 引数：table1の１件のレコード。（または，table1の１件のレコードの「hoge」の値。）
  • 返り値：真偽値。


という関数。


サブクエリの表す「関数」には引数が無い。

引数を変えて繰り返し実行する必要が無いので，１回だけ実行すれば，あとで結果を使いまわせる。


（３）EXISTSで相関サブクエリ

ここで，相関サブクエリが現れる。

今までと同じ事を，相関サブクエリを使って行なってみる。

SELECT
	table1.fuga
FROM
	table1
WHERE
	EXISTS (
		SELECT
			*
		FROM
			table2
		WHERE
			table1.hoge = table2.hoge
	)
;


EXISTS ( サブクエリ ) の部分は「関数」のようなものである，と考えるとわかりやすい。



( サブクエリ )　が表すもの：

  • 引数：table1の１件のレコード。（もしくは，それら各々の「table1.hoge」の値。）
  • 返り値：table2の，絞込み済みの複数件のレコード。


という関数。


EXISTS ( サブクエリ )　が表すもの：

  • 引数：table1の１件のレコード。（もしくは，それら各々の「table1.hoge」の値。）
  • 返り値：真偽値。


という関数。

（３）のサブクエリの表す「関数」には引数があるので，table1の各行ごとに，毎回実行する必要がある。


サブクエリの中でWHEREで条件を絞り込んでいるかどうか，という点で，（２）と（３）は異なる。


この場合，いきなりサブクエリの内側から読み進めようとしても，うまくいかない。

FROM句内にはtable2としか書いていないのに，「table1」というどこにも書かれていないテーブルがいきなりWHERE句内に出現するから，戸惑ってしまうのだ。

それも当然。table1というのは，サブクエリが受け取る「引数」のようなものだから，サブクエリの外側を見ない限り，table1の意味はわからないのだ。


これが相関サブクエリ。

サブクエリの内側と外側が，互いに関連しあっているのである。


サブクエリの中身を「関数」とみなした場合・・・

  • 非相関サブクエリの場合は，一度実行すればよい。
  • 相関サブクエリの場合は，毎回実行する必要がある。

他のサンプル

相関サブクエリの読み方の練習。


サブクエリの基本　7.5. 相関サブクエリ
http://www.techscore.com/tech/sql/07_...
の sample.18-6

この場合，サブクエリが表している関数は・・・

  • 引数：AAの１件のレコードの商品コード。
  • 返り値：該当商品の納品日。

という関数。


商品コードを渡すと納品日を返してくれるような「関数」を作っておいて，

さらにその返り値が'20010401'になるかどうかをチェックしている。

相関サブクエリで行と行を比較する
http://codezine.jp/article/detail/907
の，最初に出てくるSQL（前年と年商が同じ年度を求める）

この場合，サブクエリが表している関数は・・・

  • 引数：S1の１件のレコードの年度。
  • 返り値：該当年度の前の年の売り上げ。

という関数。


年度を渡すと前の年の売り上げを返してくれるような「関数」を作っておいて，

さらにその返り値が該当年度の売り上げと一致するかどうかをチェックしている。

巨大SQLの読み方

サブクエリが何重にもネストされているような，巨大なSQLの読み方。

  • （１）最も内側にネストされたサブクエリから，外側に向かって読み進めていく。
    • 内側の結果を使って，外側の計算が行なわれているから。
    • いつも内側から外側へ向かって「情報が流れて」いく。
  • （２）基本的には，外側を読んでいるときに，内側を再度読み直す必要が生じない。
    • 非相関サブクエリは，一度しか実行されないから。
    • サブクエリが一回結果を出したら，もうそのサブクエリを再度実行する必要は生じない。
  • （３）内側から読み進めていった時に，急に「未定義の情報」が現れる場合がある。
    • もしその情報が外側のクエリで定義されている場合，それは相関サブクエリである。
      • 内側から外側ではなく，ここはいったん逆に，外側から内側へ向かって読み進める必要がある。



関連する記事：

インデックスを作成して，SQLの速度をチューニングする手順　（PostgreSQLで，EXPLAIN文とCREATE INDEX文によるパフォーマンス改善）
http://language-and-engineering.hatenablog.jp/entry/20110121/p1


データベースとSQLの業務スキルレベル　判別表　（５段階）
http://language-and-engineering.hatenablog.jp/entry/20110320/p1


SQLで，テーブルに存在しない複数行のデータを取得する方法　（PostgreSQLの generate_series関数をマスターしよう）
http://language-and-engineering.hatenablog.jp/entry/20100309/p1


PostgreSQLのシステムテーブル入門　（暗記用のSQL集）
http://language-and-engineering.hatenablog.jp/entry/20100220/p1

　

language_and_engineering 読者になる

広告を非表示にする
スポンサーリンク
スポンサーリンク