【PosgreSQL】相関サブクエリについて。

この前、新人の後輩君に「このSQLがイメージつかない」といわれ、教えたのでその際の備忘録です。

相関サブクエリとは

相関サブクエリは、
通常のサブクエリと違い、「外部クエリへの参照が含まれている」クエリです。

外部への参照ってどういうことかというと、下のSQLみたいな感じです。

SELECT　A.name, A.age, A.prefecture FROM user_table A 
WHERE A.age > ( 
   -----サブクエリ------
   SELECT AVG(age) FROM user_table B WHERE A.prefecture = B.prefecture 
);

このSQLでいうと、「A.prefecture = B.prefecture 」のところが、
サブクエリ内で外部のテーブルAを参照しているといった感じです。

SQLの実行結果

上記のテーブルの内容はこんな感じです

SELECT　A.name, A.age, A.prefecture FROM user_table A 
WHERE A.age > ( 
   -----サブクエリ------
   SELECT AVG(age) FROM user_table B WHERE A.prefecture = B.prefecture 
);

実行結果はこんな感じ

相関サブクエリの実行順

相関サブクエリの実行順はこんな感じ

①外側のFROM句のテーブルAを参照
②テーブルAの各レコードに次の処理を実施　—- ここがポイント
　③サブクエリ側テーブルBのWHERE句を実施

②→③→②→③…をテーブルAのレコード文すべてに実施

SELECT　A.name, A.age, A.prefecture FROM user_table A 　------ ①
WHERE A.age > ( 
   -----サブクエリ------
   SELECT AVG(age) FROM user_table B WHERE A.prefecture = B.prefecture ------ ②、③
);

ポイントは、

テーブルAで取得した各レコードに対して、サブクエリを実行することです。

デメリット

あまり書く機会はない相関サブクエリですが、
書かないのにはそれなりのデメリットがあります。

自分の感じるデメリットは、
「実行速度が遅くなる」と「難読性が高くなる」ことです。

エンジニアとして、実行速度が遅くなることが分かっている書き方を優先して書くことはないのではないでしょうか。

また、難読性についても同様で、チームで開発している以上は
他の人のことも考えたリーダブルコードを心がける必要があります。

そのような理由から、あまり使われないのだろうと思います。
まあ、書かないといけない場合には使いますが。

おわりに

新人君もなかなかいいところに疑問を持ってくれたなと思います。
感覚やなんとなくでスルーしてもいいことでしたが、きちんと疑問を持って質問をしてくれてよかったです。

彼が理解するまで、２時間ほどマンツーマンで説明をすることになりましたが、自分の理解度の確認と再度きちんと調べることができたので、こちらも学ぶことができました。