cross apply & outer apply

来源：互联网发布：tomcat常用端口编辑：程序博客网时间：2024/05/03 13:36

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 1The multi-part identifier "T1.column_a" could not be bound.

最后的这个查询的语法有错误。在 cross join 时，表值函数的参数不能是表 T1 的字段，为啥不能这样做呢？我猜可能微软当时没有加这个功能：），后来有客户抱怨后，于是微软就增加了 cross apply 和 outer apply 来完善，请看 cross apply, outer apply 的例子：

-- 4. cross applyselect *  from TABLE_1 T1 cross apply FN_TableValue(T1.column_a)-- 5. outer applyselect *  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 applyselect *  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           01           HR            2           5           Steven        2           11           HR            2           6           Michael       2           12           Marketing     7           7           Robert        3           02           Marketing     7           11          David         7           12           Marketing     7           12          Ron           7           12           Marketing     7           13          Dan           7           12           Marketing     7           14          James         11          23           Finance       8           8           Laura         3           04           R&D           9           9           Ann           3           05           Training      4           4           Margaret      1           05           Training      4           10          Ina           4           1(12 row(s) affected)

-- outer applyselect *  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           01           HR            2           5           Steven        2           11           HR            2           6           Michael       2           12           Marketing     7           7           Robert        3           02           Marketing     7           11          David         7           12           Marketing     7           12          Ron           7           12           Marketing     7           13          Dan           7           12           Marketing     7           14          James         11          23           Finance       8           8           Laura         3           04           R&D           9           9           Ann           3           05           Training      4           4           Margaret      1           05           Training      4           10          Ina           4           16           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 valuescreate table Employees(  empid   int         not null,  mgrid   int         NULL,  empname varchar(25) not null,  salary  money       not null)go-- create Departments table and insert valuescreate table Departments(  deptid    int not null primary key,  deptname  varchar(25) not null)go-- fill datasinsert into employees values(1 , NULL, 'Nancy'   , 000.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-- table-value functioncreate 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)asbegin  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  returnendgo-- cross apply queryselect *  from Departments as D cross apply fn_getsubtree(D.deptmgrid) as ST-- outer apply queryselect *  from Departments as D outer apply fn_getsubtree(D.deptmgrid) as ST