CROSS APPLY和 OUTER APPLY 區別詳解

標籤：values   集中   ble   測試   結果   這一   arc   ber   employees   

SQL Server 2005 新增 cross apply 和 outer apply 聯結語句，增加這兩個東東有啥作用呢？ 

我們知道有個 SQL Server 2000 中有個 cross join 是用於交叉聯結的。實際上增加 cross apply 和 outer apply 是用於交叉聯結資料表值函式（返回表結果集的函數）的， 更重要的是這個函數的參數是另一個表中的欄位。這個解釋可能有些含混不請，請看下面的例子： 

-- 1. cross join 聯結兩個表
select *
  from TABLE_1 as T1
cross join TABLE_2 as T2
-- 2. cross join 聯結表和資料表值函式，資料表值函式的參數是個“常量”
select *
  from TABLE_1 T1
cross join FN_TableValue(100)
-- 3. cross join  聯結表和資料表值函式，資料表值函式的參數是“表T1中的欄位”
select *
  from TABLE_1 T1
cross join FN_TableValue(T1.column_a)

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T1.column_a" could not be bound.
最後的這個查詢的文法有錯誤。在 cross join 時，資料表值函式的參數不能是表 T1 的欄位， 為啥不能這樣做呢？我猜可能微軟當時沒有加這個功能：），後來有客戶抱怨後， 於是微軟就增加了 crossapply 和 outer apply 來完善，請看 cross apply, outer apply 的例子： 

-- 4. cross apply
select *
  from TABLE_1 T1
cross apply FN_TableValue(T1.column_a)

-- 5. outer apply
select *
  from TABLE_1 T1
outer apply FN_TableValue(T1.column_a)
cross apply 和 outer apply 對於 T1 中的每一行都和派生表（資料表值函式根據T1當前行資料產生的動態結果集） 做了一個交叉聯結。cross apply 和 outer apply 的區別在於： 如果根據 T1 的某行資料產生的派生表為空白，cross apply 後的結果集 就不包含 T1 中的這行資料，而 outer apply 仍會包含這行資料，並且派生表的所有欄位值都為 NULL。 

下面的例子摘自微軟 SQL Server 2005 線上說明，它很清楚的展現了 cross apply 和 outerapply 的不同之處： 

-- cross apply
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

(12 row(s) affected)
-- outer apply
select *
  from Departments as D
outer 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
6           Gardening     NULL        NULL        NULL          NULL        NULL

(13 row(s) affected)
注意 outer apply 結果集中多出的最後一行。 當 Departments 的最後一行在進行交叉聯結時：deptmgrid 為 NULL，fn_getsubtree(D.deptmgrid) 產生的派生表中沒有資料，但 outer apply 仍會包含這一行資料，這就是它和 cross join 的不同之處。 

下面是完整的測試代碼，你可以在 SQL Server 2005 線上說明上找到： 

-- create Employees table and insert values
IF OBJECT_ID(‘Employees‘) IS NOT NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
empid INT NOT NULL,
mgrid INT NULL,
empname VARCHAR(25) NOT NULL,
salary MONEY NOT NULL
)
GO
IF OBJECT_ID(‘Departments‘) IS NOT NULL
DROP TABLE Departments
GO
-- create Departments table and insert values
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT
)
GO

-- fill datas
INSERT INTO employees VALUES  (1,NULL,‘Nancy‘,00.00)
INSERT INTO employees VALUES  (2,1,‘Andrew‘,00.00)
INSERT INTO employees VALUES  (3,1,‘Janet‘,00.00)
INSERT INTO employees VALUES  (4,1,‘Margaret‘,00.00)
INSERT INTO employees VALUES  (5,2,‘Steven‘,00.00)
INSERT INTO employees VALUES  (6,2,‘Michael‘,00.00)
INSERT INTO employees VALUES  (7,3,‘Robert‘,00.00)
INSERT INTO employees VALUES  (8,3,‘Laura‘,00.00)
INSERT INTO employees VALUES  (9,3,‘Ann‘,00.00)
INSERT INTO employees VALUES  (10,4,‘Ina‘,00.00)
INSERT INTO employees VALUES  (11,7,‘David‘,00.00)
INSERT INTO employees VALUES  (12,7,‘Ron‘,00.00)
INSERT INTO employees VALUES  (13,7,‘Dan‘,00.00)
INSERT INTO employees VALUES  (14,11,‘James‘,00.00)

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)
GO
--SELECT * FROM departments

-- table-value function
IF OBJECT_ID(‘fn_getsubtree‘) IS NOT NULL
DROP FUNCTION  fn_getsubtree
GO
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) 
RETURNS TABLE 
AS 
RETURN(
  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
  )
    SELECT * FROM Employees_Subtree
)
GO

-- cross apply query
SELECT *
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST



-- outer apply query
SELECT *
FROM Departments AS D
    OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST

CROSS APPLY和 OUTER APPLY 區別詳解