SQL Server 2008的cross apply 和 outer apply

我们知道有个 SQL Server 2000 中有个 cross join 是用于交叉联接的。实际上增加
cross apply 和 outer apply 是用于交叉联接表值函数（返回表结果集的函数）的，
更重要的是这个函数的参数是另一个表中的字段。这个解释可能有些含混不请，请看下面的例子：

-- 1. cross join
联接两个表
select*
  from TABLE_1
as
T1
crossjoin TABLE_2 as T2
-- 2. cross join
联接表和表值函数，表值函数的参数是个“常量”
select*
  from TABLE_1
T1
crossjoin FN_TableValue(100)
-- 3. cross join  联接表和表值函数，表值函数的参数是“表T1中的字段”
select*
  from TABLE_1
T1
crossjoin FN_TableValue(T1.column_a)

Msg 4104, Level16, State 1,
Line 1
The multi-part identifier
"T1.column_a" could not be bound.
最后的这个查询的语法有错误。在 crossjoin 时，表值函数的参数不能是表 T1
的字段， 为啥不能这样做呢？我猜可能微软当时没有加这个功能：），后来有客户抱怨后， 于是微软就增加了 cross apply 和
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 和
outer
apply 的不同之处：

-- 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
仍会包含这一行数据，这就是它和 crossjoin 的不同之处。

下面是完整的测试代码，你可以在 SQL Server 2005 联机帮助上找到：

-- create Employees
table and insert values
IFOBJECT_ID('Employees') ISNOTNULL
DROPTABLE
Employees
GO
CREATETABLE
Employees
(
empid INTNOTNULL,
mgrid INTNULL,
empname
VARCHAR(25) NOTNULL,
salary MONEYNOTNULL
)
GO
IFOBJECT_ID('Departments') ISNOTNULL
DROPTABLE
Departments
GO
-- create Departments table and insert values
CREATETABLE
Departments
(
deptid INTNOTNULLPRIMARYKEY,
deptname VARCHAR(25) NOTNULL,
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
IFOBJECT_ID('fn_getsubtree') ISNOTNULL
DROPFUNCTION 
fn_getsubtree
GO
CREATE  FUNCTION
dbo.fn_getsubtree(@empidASINT)
RETURNSTABLE

AS

RETURN(
  WITH
Employees_Subtree(empid, empname, mgrid, lvl)
  AS
  (
   
--
Anchor Member (AM)
    SELECT empid,
empname, mgrid, 0
    FROM employees
   
WHERE
empid =
@empid  

    UNIONALL
    -- 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