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
------
0
1

これで、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」にすれば取得可能となる。

イラストで理解　SQL　はじめて入門

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2019/05/16
• メディア: 単行本（ソフトカバー）

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載ってます。

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

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ではできるか不明。

関連記事

SQL NATURAL JOIN 自然結合 (というか結合のあれこれ)
SQL WITH句 再帰クエリー RECURSIVE SQLポケリ
SQLポケットリファレンスの歴史
SQLポケットリファレンスの歴史その2
SQLポケットリファレンスが第4版になりました
改訂第4版 SQLポケットリファレンス電子版
ProxmoxにOracle23cを入れてみる【OL9には入らない】
データベースにおける正規表現【REGEXP】

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

イラストで理解　SQL　はじめて入門

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2019/05/16
• メディア: 単行本（ソフトカバー）

---

サイト内を検索

タグ：分析関数 SQL