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

联接表和表值函数，表值函数的参数是个“常量”

crossjoin FN_TableValue(100)

-- 3. cross join  联接表和表值函数，表值函数的参数是“表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

cross apply FN_TableValue(T1.column_a)

-- 5. outer

apply

outer apply FN_TableValue(T1.column_a)

cross apply 和

apply 对于 T1 中的每一行都和派生表（表值函数根据T1当前行数据生成的动态结果集） 做了一个交叉联接。cross apply 和

apply 的区别在于： 如果根据 T1 的某行数据生成的派生表为空，cross apply 后的结果集

就不包含 T1 中的这行数据，而 outer apply 仍会包含这行数据，并且派生表的所有字段值都为 NULL。

下面的例子摘自微软 SQL Server 2005 联机帮助，它很清楚的展现了 cross apply 和

apply 的不同之处：

-- cross apply

  from Departments

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

outer apply fn_getsubtree(D.deptmgrid) as ST

6           Gardening     NULL       

NULL        NULL         

NULL        NULL

(13

注意 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

(

empid INTNOTNULL,

mgrid INTNULL,

empname

VARCHAR(25) NOTNULL,

salary MONEYNOTNULL

)

IFOBJECT_ID('Departments') ISNOTNULL

Departments

-- create Departments table and insert values

deptid INTNOTNULLPRIMARYKEY,

deptname VARCHAR(25) NOTNULL,

deptmgrid INT

-- fill

datas

INSERT  INTO employees

VALUES 

(1,NULL,'Nancy',00.00)

(2,1,'Andrew',00.00)

(3,1,'Janet',00.00)

(4,1,'Margaret',00.00)

(5,2,'Steven',00.00)

(6,2,'Michael',00.00)

(7,3,'Robert',00.00)

(8,3,'Laura',00.00)

(9,3,'Ann',00.00)

(10,4,'Ina',00.00)

(11,7,'David',00.00)

(12,7,'Ron',00.00)

(13,7,'Dan',00.00)

(14,11,'James',00.00)

INSERT  INTO departments

(1,'HR',2)

(2,'Marketing',7)

(3,'Finance',8)

(4,'R&D',9)

(5,'Training',4)

(6,'Gardening',NULL)

--SELECT * FROM

departments

-- table-value function

IFOBJECT_ID('fn_getsubtree') ISNOTNULL

DROPFUNCTION 

fn_getsubtree

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

e

       join employees_subtree AS es

ON

e.mgrid = es.empid

  )

    SELECT*FROM

Employees_Subtree

query

SELECT  *

FROM Departments

    CROSS APPLY fn_getsubtree(D.deptmgrid) AS

ST

-- outer apply query

    OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST