◆◇◇◆━━━━━━━━━━━━━━━━━━━━2009.12.09━━━・‥…
えす！エス！レスキュー SQL Server
-どっぷりリファレン中！！-
・‥…━━━━━━━━━━━━━━━━━━━━━Vol. 6━━━━◆◇◇◆
<<目次>>
■SQL Server リファレン中！！ その6
■お知らせ・・・○ご質問について
■編集者より
◎バックナンバーココから！！
http://www.insight-tec.com/mailmagazine/ssql/index.html
┏─────────────────────────────────┓
┃SQL Server リファレン中！！ ┃
┗─────────────────────────────────┛
======================================================================
アジェンダ
======================================================================
今回のメルマガでは以下の内容をお伝えします。
・APPLYとは
======================================================================
APPLYとは
======================================================================
SQL Server 2005から「APPLY」と言うものが使用できるようになりました。
# MSDN → http://msdn.microsoft.com/ja-jp/library/ms175156.aspx
# 何気に、vol5のメルマガで使用していましたね♪
APPLYは、SQLで取得した「行毎」に、
「テーブル値関数を呼び出す」ことが出来ます。
言い換えるとAPPLYの利点は、
行毎に列データを、テーブル値関数の引数に出来ることです。
今まではTransact-SQLのスクリプトを組み、
取得したデータの件数分、ループを回しながら
列の値を引数としてテーブル値関数を呼び出し、
結果の取得とを結合を行って返却していたと思いますが、
それが、とても簡単なSQLで対応できるようになったのです。
APPLYはJOINに似ていて、APPLY使い方が2種類あります。
・CROSS APPLY
データ行毎にテーブル値関数を呼び出し、
テーブル値関数との結果を結合して出力するが、
テーブル値関数のデータがない場合は、
そのデータ行は出力されない。
・OUTER APPLY
データ行毎にテーブル値関数を呼び出し、
テーブル値関数との結果を結合して出力するが、
テーブル値関数のデータがない場合は、
テーブル値関数の列部分にNULLが設定された1行と
結合して結果を出力する。
上記内容を分りやすく一言で言ってしまえば、
JOIN = CROSS APPLY
OUTER JOIN = OUTER APPLY
となります。
では、そのAPPLYを実際に使ってみようと思います。
最初に、使用するテーブルとデータの作成、
及び、テーブル値関数の作成を行います。
作成するテーブル値関数「GET_CHILD」は、
引数で渡されたidをparentidとして持つ
tb1のデータを返却するようになっています。
<< SQL文 >>
/********************************************************************/
CREATE TABLE [tb1](
[id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[val] [nvarchar](50) NULL,
[parentid] [decimal](18, 0) NULL,
CONSTRAINT [PK_tb1] PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
GO
INSERT INTO tb1 (val,parentid) VALUES(N'TOP',null); INSERT INTO tb1 (val,parentid) VALUES(N'CATEGORY1', 1); INSERT INTO tb1 (val,parentid) VALUES(N'CATEGORY2', 1); INSERT INTO tb1 (val,parentid) VALUES(N'CATEGORY3', 1); INSERT INTO tb1 (val,parentid) VALUES(N'SUB_CATEGORY1.1',2); INSERT INTO tb1 (val,parentid) VALUES(N'SUB_CATEGORY1.2',2); INSERT INTO tb1 (val,parentid) VALUES(N'SUB_CATEGORY1.3',2); INSERT INTO tb1 (val,parentid) VALUES(N'SUB_CATEGORY2.1',3); GO
CREATE FUNCTION GET_CHILD(@id decimal(18,0)) RETURNS @ret TABLE (
id decimal(18,0),
val nvarchar(50),
parentid decimal(18,0)
)
BEGIN
INSERT INTO @ret SELECT id, val, parentid
FROM tb1 WHERE parentid = @id;
RETURN ;
END;
GO
/********************************************************************/
まずはCROSS APPLYからです。
記述方法は簡単で、
「テーブル名 CROSS APPLY テーブル値関数(テーブル名.列名) 」
と書くだけです。
<< SQL文 >>
/********************************************************************/
SELECT
*
FROM
tb1
CROSS APPLY
GET_CHILD(tb1.id);
/********************************************************************/
<< 実行結果 >>
/********************************************************************/
id val parentid id val parentid
-- --------- -------- -- --------------- --------
1 TOP NULL 2 CATEGORY1 1
1 TOP NULL 3 CATEGORY2 1
1 TOP NULL 4 CATEGORY3 1
2 CATEGORY1 1 5 SUB_CATEGORY1.1 2
2 CATEGORY1 1 6 SUB_CATEGORY1.2 2
2 CATEGORY1 1 7 SUB_CATEGORY1.3 2
3 CATEGORY2 1 8 SUB_CATEGORY2.1 3
/********************************************************************/
これだけで、上記結果のようにテーブル値関数の結果と
結合した形で結果が取得できます。
データ1行に対して、テーブル値関数の返却データが複数行の場合は、
テーブル値関数の行数分、データ行が繰り返されて出ている事も
確認できるかと思います。
次にCROSS APPLYをOUTER APPLYに書きかえて実行します。
<< SQL文 >>
/********************************************************************/
SELECT
*
FROM
tb1
OUTER APPLY
GET_CHILD(tb1.id);
/********************************************************************/
<< 実行結果 >>
/********************************************************************/
id val parentid id val parentid
-- -------------- -------- ---- --------------- --------
1 TOP NULL 2 CATEGORY1 1
1 TOP NULL 3 CATEGORY2 1
1 TOP NULL 4 CATEGORY3 1
2 CATEGORY1 1 5 SUB_CATEGORY1.1 2
2 CATEGORY1 1 6 SUB_CATEGORY1.2 2
2 CATEGORY1 1 7 SUB_CATEGORY1.3 2
3 CATEGORY2 1 8 SUB_CATEGORY2.1 3
4 CATEGORY3 1 NULL NULL NULL
5 SUB_CATEGORY1.1 2 NULL NULL NULL
6 SUB_CATEGORY1.2 2 NULL NULL NULL
7 SUB_CATEGORY1.3 2 NULL NULL NULL
8 SUB_CATEGORY2.1 3 NULL NULL NULL
/********************************************************************/
このようにOUTER APPLYにすると、テーブル値関数がデータを返さない部分
(idが4～8のデータ)も出力されます。
APPLYの動きを踏まえて、再度vol6のSQLとやっていることを
確認していただけたら幸いです。
======================================================================
まとめ
======================================================================
・APPLYは取得したデータ行毎に、
テーブル値関数を実行させることが出来る。
・テーブル値関数の戻りデータがない場合に、
そのデータ行を出力したくない場合は、CROSS APPLYを使用する。
・テーブル値関数の戻りデータがない場合でも、
そのデータ行を出力したい場合は、OUTER APPLYを使用する。
┏─────────────────────────────────┓
┃ご質問について ┃
┗─────────────────────────────────┛
＜皆様からのご質問を受付けております＞
皆様のご質問にはできるだけ、お答えしたいと思っています。
すべてのご質問にお答えすることはできないかもしれませんが、
適宜メルマガ内でとりあげていく予定ですので、是非お気軽に下記アドレス
までお寄せください。
ご意見、ご感想などもお待ちしておりますっ！！
mailto:letter@insight-tec.co.jp
┏─────────────────────────────────┓
┃編集者より ┃
┗─────────────────────────────────┛
12月に入り、冷え込む日が増えてきましたね。
クールビズの冬版ウォームビズもオフィスに定着しつつあり、室内と言えども
なんらかの防寒対策をしないと過ごしづらくなっています。
寒さには比較的強い私も、そろそろ膝かけと座布団の導入を検討中。
ホットカーペット機能搭載の座布団に誘惑されています。
by MS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜えす！エス！レスキュー SQL Server -どっぷりリファレン中！！-＞
発行／編集：株式会社インサイトテクノロジー
http://www.insight-tec.com
mailto:letter@insight-tec.co.jp
本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright(c) 2009, Insight Technology, Inc., All Rights Reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
