select句の相関サブクエリ

※以後取得されるsqlはpostgresqlで実証

親テーブルのカラムが含まれるサブクエリ

例えばtable t1(f1,f2,f3),t2(f1,f2,f3)を考える。

テーブル名 :t1

f1	f2	f3
1	20	30
1	200	300
1	2000	3000

テーブル名 :t2

f1	f2	f3
1	20	30
1	222	333
1	2222	3333

select tt1.f1
     , ( select tt2.f1
           from t2 tt2
          where tt2.f2 = tt1.f2) 
  from t1 tt1

これはごく一般的な相関サブクエリですが、このとき検索条件が同じでかつほかのカラムも返したいときにはどうするのか？

返したいカラムの分だけ相関サブクエリを記述しなければいけません。

select tt1.f1
     , ( select tt2.f1
           from t2 tt2
          where tt2.f2 = tt1.f2 )
     , ( select tt2.f3
           from t2 tt2
          where tt2.f2 = tt1.f2 )
 from t1 tt1

これは、正直かなり冗長だと多くの人が感じてるところです。
10カラムを返したい場合は、10の相関サブクエリが必要になります。
関数化でまとめることもできますが、SQL外の処理になります。
また、返すカラム値を文字列結合してインラインビューにし、再度分割処理を入れることも可能です。また、postgresqlでは配列関数を利用することも可能です。

select
       split_part( ttt1.tt2_f1f2, '#',1)
     , split_part( ttt1.tt2_f1f2, '#',2)
from (
        select tt1.f1
             , ( select tt2.f1 || '#' || tt2.f2
                   from t2 tt2
                  where tt2.f2 = tt1.f2 ) tt2_f1f2
         from t1 tt1
) ttt1

文字列結合の場合はサブクエリが一つになるのはとてもシンプルですが、すべて文字列結合⇒再度、カラム別に分割になるところがイマイチだと思います。

カラムの文字列結合なしに複数カラムを一括取得する方法とは。

SQLの再帰処理をうまく利用すればできます。
サンプルとして以下を考えます。

テーブル：cmain

n	d	key1	key2
1	11	111	1111
2	22	222	2222
3	33	333	3333

テーブル：csubt

d	dd	key1	key2
1100	101	111	1111
2200	202	222	2222
3300	303	333	3333

with RECURSIVE  cmain AS
  ( SELECT 1 as n, 11 as d, 111 as key1 ,1111 as key2 
  UNION ALL
    SELECT 2 as n, 22 as d ,222 as key1, 2222 as key2 
  UNION ALL
    SELECT 3 as n, 33 as d,333 as key1,3333 as key2 
  )
  , csubt AS
  (
    SELECT  1100 as d,101 as dd, 111 as key1 ,1111 as key2 
  UNION ALL
    SELECT  2200 as d,202 as dd, 222 as key1 ,2222 as key2 
  UNION ALL
    SELECT  3300 as d,303 as dd, 333 as key1 ,3333 as key2 
  )
  , csub AS
  (
    SELECT 0          as cnt
        , 0           as n           
        , 0           as d     
        , 0           as dd 
        , 0           as key1
        , 0           as key2
    UNION ALL
    SELECT 1          as cnt
        , cb.n + 1    as n
        , ct.d        as d
        , ct.dd       as dd
        , ct.key1     as key1
        , ct.key2     as key2
      FROM csubt ct
 LEFT JOIN cmain cm
        ON cm.key1 = ct.key1
       AND cm.key2 = ct.key2
  right JOIN csub cb
        ON cb.n + 1 = cm.n
     where cb.n < ( SELECT COUNT(*) FROM cmain )
  )
    SELECT cm.n as cm_n
         , cm.d as cm_d
         , cm.key1 as cm_key1
         , cm.key2 as cm_key2
         , cb.d as cb_d
         , cb.dd as cb_dd
     FROM cmain cm
left join csub cb
       on cm.key1 = cb.key1
      and cm.key2 = cb.key2
    where cb.cnt > 0

cm_n	cm_d	cm_key1	cm_key2	cb_d	cb_dd
1	11	111	1111	1100	101
2	22	222	2222	2200	202
3	33	333	3333	3300	303

解説

まず、with rcursiveで再帰処理を宣言します。再帰処理の細かい仕様については、同じqiitaにも説明がありますので割愛します。また時間があるときに、別途追記するかもしれません。

最初の２つcmain ,csubtは、以後使用するテーブルデータを作成しているだけです。
次にcsubで実際の再帰処理をしています。
union allより前は再帰処理前の処理になります。

-- union all 前の処理
 select 0 as cnt
      , 0 as n
      , 0 as d
      , 0 as dd

ここでやっていることは、なにもありません。最終的に　 cnt > 0で削除されます。
再帰処理の一回目のクエリになりますが、こちらにもunion all以降と同等の処理を書いて cnt >0を行わないようにすることも可能ですが、sqlが冗長になるためあえてこの記述にしました。

そして結論からいうと、select句のサブクエリを複数カラム取得する方法は、、、テーブル結合に置き換えることにほかなりません。union all以降の再帰処理で一行づつ複数カラムを取得しています。
最後に、ユニークな行番号でサブクエリの結果を結合します。
また、結合には、key1,key2でも問題ないはずです。

相関サブクエリにcount, max,　limit, order by などが使用されている場合

union all の再帰項で同時に処理するのは文法違反です。
この場合、csubの結果を使用してwindow関数などで処理を置き換えれば可能です。
※また別途記述したいと思います。

まとめ

文字列結合で相関サブクエリを一括取得するのはシンプルです。
取得するカラムが2,3個の場合検索条件が相当複雑にならない限りは、それぞれ別々で取得でもいいのかなと思いますが、5，6個、、、と増えていくと一括取得も考えてみてはと思います。
postgresqlでは、配列関数で取得し、再度配列指定で取得できます。ただしこちらも、配列値の型は同一なもの一つだけになります。なので、数値、文字列混在するカラムの一括取得の場合はどれか一つの型に寄せる必要があります。
今回のように相関サブクエリ(correlated subqueries)で条件(where句)は同一なのにカラム名だけ違うsqlをべた書きするのは冗長すぎると思ってる人は多いと思われます。その解決方法として再帰SQLを使用した結合処理への置き換えによって複数カラムの一括取得は可能であることは示せました。

新発見
その後、まさに相関サブクエリのカラムを一括で取得するためにlateral(cross apply,outer apply)句というのがありました。(_)