APPLY の使用
更新 : 2006 年 4 月 14 日
APPLY 操作では、クエリの外部テーブル式によって返される行ごとにテーブル値関数を呼び出すことができます。テーブル値関数は右入力として機能し、外部テーブル式は左入力として機能します。左入力からの行ごとに右入力が評価され、生成された行の組み合わせが最終的な出力に使用されます。APPLY 操作によって生成される列のリストは、左入力の列のセットです。その後には、右入力によって返された列のリストが続きます。
.gif "ms175156.note(ja-jp,SQL.90).gif") メモ : |
|---|
| APPLY を使用するには、データベース互換性レベルを 90 にする必要があります。 |
APPLY には、CROSS APPLY と OUTER APPLY という 2 つの形式があります。CROSS APPLY では、外部テーブルの行のうち、テーブル値関数から結果セットを生成する行のみが返されます。OUTER APPLY では、結果セットを生成する行と生成しない行の両方が返されます。そのとき、テーブル値関数によって生成される列には NULL 値が設定されます。
例として、次に示す Employees および Departments というテーブルについて考えてみましょう。
--Create Employees table and insert values.
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
)
GO
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
--Create Departments table and insert values.
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT NULL REFERENCES Employees
)
GO
INSERT INTO Departments VALUES(1, 'HR', 2)
INSERT INTO Departments VALUES(2, 'Marketing', 7)
INSERT INTO Departments VALUES(3, 'Finance', 8)
INSERT INTO Departments VALUES(4, 'R&D', 9)
INSERT INTO Departments VALUES(5, 'Training', 4)
INSERT INTO Departments VALUES(6, 'Gardening', NULL)
Departments テーブル内のほとんどの部門には、Employees テーブル内の従業員に対応するマネージャ ID が含まれています。次のテーブル値関数は、従業員 ID を引数として受け取り、対応する従業員およびその部下をすべて返します。
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM Employees AS e
JOIN Employees_Subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree
RETURN
END
GO
各部門のマネージャについて、全レベルの部下をすべて返すには、次のクエリを使用します。
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
以下に結果セットを示します。
deptid deptname deptmgrid empid empname mgrid lvl
----------- ---------- ----------- ----------- ---------- ----------- ---
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1
Departments テーブルの各行は、各部門のマネージャに対して fn_getsubtree から返された行数と同じ数だけ複製されています。
また、結果には Gardening 部門が表示されていません。これは、この部門にはマネージャが存在せず、fn_getsubtree から空のセットが返されたためです。OUTER APPLY を使用すると、Gardening 部門も結果セットに表示されます。fn_getsubtree によって返されるフィールドだけでなく、deptmgrid フィールドにも NULL 値が入ります。