CROSS APPLY (LATERAL) で関数とJOIN(結合)して見る

本日は、SQLネタである。

少し前に、結合方法について書いてみた(INNER JOINとかの記事のことです)。
NATURAL JOINが最新の結合方法、と書いてみたりしたのだが、これよりもっと新しそうなのを発見してしまった。
「CROSS APPLY」というやつである。以前からその存在は知っていた(*1)のだが、Oracle12cで採用になったのでちょっと調べてみることにした。
こんな感じで使います。

SELECT * FROM foo CROSS APPLY func_bar(foo.a) AS FB

*1
SQL Serverのマニュアルでxml列のメソッドnodes()を使うときに、CROSS APPLYを使っている。
CROSS APPLYはMS SQL Serverでは、2005からできることになっている。マニュアルには、テーブルを戻す関数をCROSS APPLYすると便利ですよ"的な"例が載っている。
そうなのか、じゃあやってみるか。

CREATE TABLE foo (
a INTEGER,
b VARCHAR(10)
);
INSERT INTO foo VALUES(1,'one');
INSERT INTO foo VALUES(2,'two');
INSERT INTO foo VALUES(3,'three');

まずは、CROSS APPLYの左側に指定するテーブルを作成してみた。このテーブルfooに対してCROSS APPLYで交差適用(とでもいうのだろうか)、をやってみたいと思う。
CROSS APPLYの右側には、テーブル値を戻す関数を指定するといいよ、とのことなので、関数を作成してみる。引数でもらった値の行数分だけ連番を戻すような関数を作成する。

CREATE FUNCTION func_bar(@no int)
RETURNS @result TABLE (a int)
AS
BEGIN
DECLARE @i int
SET @i = 0
WHILE @i < @no
BEGIN
INSERT INTO @result SELECT @i
SET @i = @i + 1
END
RETURN
END

説明しなくてもいいよね。
この関数を実行したら、以下のような結果を戻す。

SELECT * FROM func_bar(2);
a
------
01

これで、CROSS APPLYの左右が揃った。fooとfun_barを結合してみるわけであるが、テーブルfooと関数func_barを単純に結合するのではなく、foo.aをfunc_barの引数に渡して結合したいのである。
そんな時に、CROSS APPLYを使うとよいらしい。違うかなぁ...まぁ、やってみよう。

SELECT * FROM foo CROSS APPLY func_bar(foo.a) AS FB
a   b     a_1
---------------
1   one   0
2   two   0
2   two   1
3   three 0
3   three 1
3   three 2

func_barに渡した引数で戻りの行数が異なる。これを考慮して、交差結合した感じになった。
a=1の行 func_barの戻す行=1 (0)
a=2の行 func_barの戻す行=2 (0,1)
a=3の行 func_barの戻す行=3 (0,1,2)
まぁ、そういうもんでしょう。
CROSS APPLYの左右を逆にすることはできない。

SELECT * FROM func_bar(foo.a) AS FB CROSS APPLY foo

はエラーになる。foo.aが最初に出現するので、これがわからん、と文句をいわれる。
マニュアルには関数なら便利だよ的なことが書いてあるので、関数じゃなければいけないかというと、そうでもない。テーブル値を戻せばよいだけなので、サブクエリでも良い。
もうひとつテーブルを作成してみよう。

CREATE TABLE bar (
a INTEGER,
b VARCHAR(20)
)
INSERT INTO bar VALUES(1,'uno');
INSERT INTO bar VALUES(1,'一');
INSERT INTO bar VALUES(2,'due');
INSERT INTO bar VALUES(2,'二');

barテーブルを作成した。これをSELECTするサブクエリをCROSS APPLYしてみよう。

SELECT * FROM foo CROSS APPLY (
SELECT * FROM bar WHERE foo.a = bar.a
) AS SB
a   b     a_1  a_2
--------------------
1   one   1    uno
1   one   1    一
2   two   2    due
2   two   2    二

できた。
a=3の行がなくなってしまっているが、「OUTER APPLY」にすれば取得可能となる。

OUTER APPLY

SELECT * FROM foo OUTER APPLY (
SELECT * FROM bar WHERE foo.a = bar.a
) AS SB
a   b     a_1  a_2
--------------------
1   one   1    uno
1   one   1    一
2   two   2    due
2   two   2    二
3   three null null

えーと、これって普通のINNER JOINやLEFT JOINと「どう違うのだろう？」と思いません？
INNER JOINで書くとするのなら、以下のようにすればよいと思うのだが...

SELECT * FROM foo INNER JOIN (
SELECT * FROM bar
) AS SB ON foo.a = SB.a

結合条件をサブクエリのWHEREで書いているか、FROMのONで書いているかの違いはあるのか。
CROSS APPLYを単にINNER JOINには変更できない。以下のようにすると、エラーになる。

SELECT * FROM foo INNER JOIN (
SELECT * FROM bar WHERE foo.a = bar.a
) AS SB

ONで条件式を書いていない、というのは横に置いておくとしても、サブクエリの中で、fooを使うことはできない。fooはわかりません、というエラーになる。
ああ、そうか、こういう場合、SELECT句にサブクエリ書いて逃げてきたかも。でも、SELECT句に書くとスカラ値を返さなくてはいけなくなって、散々苦労した覚えが...

LATERAL

LATERALインラインビューというものがある。これを使えば、INNER JOINでもCROSS APPLYのようなことができてしまうのである。
LATERALインラインビューにするには、サブクエリの前にLATERALキーワードを付けるだけ。

SELECT * FROM foo INNER JOIN LATERAL (
SELECT * FROM bar WHERE foo.a = bar.a
) SB

SQL Serverでは、LATERALをサポートしていないので、エラーになってしまったが、Oracle12c、PostgreSQLでは実行できる。
OUTER APPLYにしたい場合は、LEFT JOINを使えば良い。

SELECT * FROM foo LEFT JOIN LATERAL (
SELECT * FROM bar WHERE foo.a = bar.a
) SB ON foo.a = SB.a

結合条件を冗長に記述しないといけないのが、ちょっと美しくないが、しょうがない。
ちなみに、LATERALはSQL標準である。CROSS APPLYばベンダ拡張。
SQLポケリ第4版にはCROSS APPLY載ってます。

2021/07/11追記
LATERALで冗長にONを書かないといけないと書いた。JOIN構文なのでONを記述しなければならないが、サブクエリと全く同じ条件を書く必要はないことがわかった。
なんなら、ON TRUEとしてしまうことも可能である。

SELECT * FROM foo LEFT JOIN LATERAL (
SELECT * FROM bar WHERE foo.a = bar.a
) SB ON TRUE

と書くこともできる。
Postgresで確認。Oracleではできるか不明。

投稿者プロフィール

asai

最新の投稿

• コンピューター関係の記事2025年4月26日アイキャッチ画像にSQLアイコンとJSONアイコンが欲しい
• コンピューター関係の記事2025年4月23日AdSense審査に無事パス
• コンピューター関係の記事2025年4月22日SSブログ復活
• コンピューター関係の記事2025年4月22日SSブログが無くなった余波は大きい