SQL サブクエリについてのメモ

サブクエリの実行順序
スカラ・サブクエリ
相関サブクエリ
相関サブクエリをテーブル結合で書き換える
相関サブクエリの実行順序イメージ
参考

サブクエリの実行順序

サブクエリは内側から先に実行されるよ。

スカラ・サブクエリ

スカラは「単一の」という意味。

「千葉県」や「17」のような単一の値のみを返すサブクエリをスカラサブクエリという。

スカラサブクエリはスカラ値を書けるとこならどこでも書ける。

SELECT　WHERE　HAVING　ORDER BYなどなど。

相関サブクエリ

イメージとしてはグループごとの比較・集計をするときに使う。
外側のクエリから1行ずつレコードを取り出し、その値をサブクエリに渡して評価する。

例：各店舗平均価格より上の価格を出力したい
　 HVING　価格 > AVG(価格 GROUP BY 店鋪）←これを実現するもの

上記をhaving句でやろうにもAVG（価格 GROUP BY 店鋪）がスカラ値にならないのでエラーになる。
そこで相関サブクエリの出番。

・相関サブクエリ

  select * from Table1 t1
  where
  t1.value >(
  select avg(t2.value) from Table1 t2
  where t1.tenpo_cd = t2.tenpo_cd --ここがポイント
  group by t2.tenpo_cd
  )

tenpo_cd1の平均126.6667
tenpo_cd1の平均190
tenpo_cd1の平均160

各店舗の平均価格超えの商品が抽出される。

相関サブクエリをテーブル結合で書き換える

相関サブクエリじゃないとできないわけではなくてテーブル結合でも同じことができる↓

・テーブル結合バージョン

select * from Table1 t1
left join (
    select tenpo_cd, avg(value) AS value from Table1
    group by tenpo_cd
    ) t2
on t1.tenpo_cd = t2.tenpo_cd --ここがポイント
where
t1.value > t2.value

上記はON句で条件を指定している。
相関サブクエリは、サブクエリ内のwhere句で条件を指定する。

個人的には相関サブクエリよりテーブル結合の方がわかりやすいと思う。

相関サブクエリの実行順序イメージ

・相関サブクエリ

  select * from Table1 t1
  where
  t1.value >(
  select avg(t2.value) from Table1 t2
  where t1.tenpo_cd = t2.tenpo_cd --ここがポイント
  group by t2.tenpo_cd
  )

・実行順序への疑問
内側から先に実行されるなら「 where t1.tenpo_cd = t2.tenpo_cd」の部分ってどうなるの？という疑問が湧いたので。

t1.tenpo_cdは外側のクエリだから内側より後で実行される
↓
じゃあ内側では t1.tenpo_cdには何も値が入ってないのでは？という疑問。

・イメージ
外側のクエリ1行1行に対しサブクエリが実行されているイメージ。
外側のクエリから1行分の値が渡される→内側のクエリを実行してその値を評価する