相関サブクエリから親の親レベルのカラム参照がついにオフィシャルに(Oracle)

12cから相関サブクエリにおいて2段階以上上位レベルの親ステートメントカラムへの参照が可能になっていましたが、ずっとマニュアルに記載されておらず使用が躊躇われる状態でした。しかし18cでやっと更新された様です。一応以前からID 2067066.1でバグではないと言及されてはいましたが、問題なしとは書かれてなかったですから。オフィシャルに宣言されたことでこれからは大手を振って使えますね。

SQL Language Reference「副問合せの使用方法」より
<12.1/12.2>
ネストした副問合せが、その副問合せから1レベル上位の親である文で参照する表の列を参照する場合、Oracleは相関副問合せを行います。
[SQL Language Reference 12.2]
(https://docs.oracle.com/cd/E82638_01/sqlrf/Using-Subqueries.html#GUID-53A705B6-0358-4E2B-92ED-A83DE83DFD20)

<18c>
ネストした副問合せが、その副問合せ、またはネストした副問合せから1レベルまたは複数レベル上位の親である文で参照する表の列を参照する場合、Oracleは相関副問合せを行います。
SQL Language Reference 18c

つまりどういうことかというと、簡単に書くとこんな感じ。以下は一番深いサブクエリから３レベル上位の一番外側のテーブルのカラムを参照しています。

SELECT (SELECT *
        FROM   (SELECT *
                FROM   dual
                WHERE  dummy IN (SELECT *
                                 FROM   dual
                                 WHERE  dummy = s.dummy))) A -- ここから
FROM   dual s; -- これを参照

11gおよびそれ以前の環境では当然エラーです。

ERROR at line 1:
ORA-00904: "S"."DUMMY": invalid identifier

ところが、12cでは問題なく動きます。

A
-
X

1 row selected.

SQL書いてると場合によっては、どうしてもこの二段飛び参照が必要になることがあります。たとえば、分析関数ですが、集合関数や分析関数への引数として分析関数を使用することはできません。以下のように、ORA-30483です。

エラー

SELECT (SELECT MAX(SUM(qty)
                         over (
                           ORDER BY order_date 
                              ROWS BETWEEN 1 preceding
                                       AND 1 following
                         ))
                FROM   test_orders
                WHERE  item = t.item and order_id > t.order_id)
              ) max_sum
FROM   test_orders t;

------------------------------
ORA-30483: window functions are not allowed here

で、仕方がないのでサブクエリをもう一段階ふやすと11gでは上記の「参照できない」エラーになりやがってたわけです。

11gだとエラー

SELECT (
        SELECT MAX(s)
        FROM   (
                SELECT SUM(qty)
                         over (ORDER BY order_date 
                               ROWS BETWEEN 1 preceding 
                                        AND 1 following) s
                FROM   test_orders
                WHERE  item = t.item and order_id > t.order_id
                )
       ) max_sum
FROM   test_orders t;

------------------------------
ORA-00904: "T"."ITEM": invalid identifier

他にも、階層であったり再帰であったり。

階層

SELECT (SELECT id
        FROM   (SELECT id
                FROM   test_tbl
                WHERE  id BETWEEN t.id AND t.id * 2) -- 階層への前提条件
        WHERE  connect_by_isleaf = 1
-- AND id BETWEEN t.id AND t.id * 2 -- 前提条件をここに書くと結果が異なる
        START WITH id = t.id
        CONNECT BY PRIOR id + 1 = id) m
FROM   test_tbl t
WHERE  id IN ( 3, 5 );

再帰

WITH 
RECUR(n) AS (SELECT columnA FROM test_tbl
            UNION ALL
            SELECT (
                   -- ここに複雑な動きを書くと多重ネストクエリになりがち
                   )
            FROM RECUR, test_tbl -- 自己参照は第一レベルのクエリというルール
)
SELECT * FROM RECUR;

まぁ、ユーザ定義ファンクションを併用したりサブクエリをUNNEST（インラインビュー化）すればええんでない？って話もあるんですが、そうするとパフォーマンスの問題に直面したりもします。どうしても相関したい時もあるんですよね。

ちなみに、LATERAL句にもちゃんと適用されるようです。

ラテラル句

SELECT *
FROM   test_orders t,
       lateral (
          SELECT MAX(s)
          FROM   (
                  SELECT SUM(qty)
                         over (ORDER BY order_date 
                               ROWS BETWEEN 1 preceding 
                                        AND 1 following) s
                  FROM   test_orders
                  WHERE  item = t.item and order_id > t.order_id
                )
       )

しかしなんというか、マニュアルもサラッと流してますけど、SQLの使い勝手的には相当にインパクトのあるエンハンスメントじゃないかと思うんですがいかかでしょう？

以上。