So-net無料ブログ作成
検索選択
前の1件 | -

CROSS APPLY LATERAL [SQLポケリ]

本日も、SQLネタである。
少し前に、結合方法について書いてみた(INNER JOINとかの記事のことです)。
NATURAL JOINが最新の結合方法、と書いてみたりしたのだが、これよりもっと新しそうなのを発見してしまった。

CROSS APPLY」というやつである。以前からその存在は知っていた(*1)のだが、Oracle12cで採用になったのでちょっと調べてみることにした。

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

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 CROSS 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ばベンダ拡張。


nice!(1)  コメント(0) 
前の1件 | -



AREarthroid
AREarthroidPro ARで地球を表示するアプリ
AREarthroid ARで地球を表示するアプリ(無料)
AREarthライブ壁紙(無料)
Copyright Atsushi Asai Google+朝井淳

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2015/03/03
  • メディア: 単行本(ソフトカバー)
C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2011/04/08
  • メディア: 単行本(ソフトカバー)

【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)

【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2009/04/29
  • メディア: 単行本(ソフトカバー)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

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

この広告は前回の更新から一定期間経過したブログに表示されています。更新すると自動で解除されます。