cross apply tb表都存在姓名的情况下才出现
outer apply tb外表存在的都显示
create table #T(姓名 varchar(10))
insert into #T values('张三')
insert into #T values('李四')
insert into #T values(NULL )
create table #T2(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into #T2 values('张三' , '语文' , 74)
insert into #T2 values('张三' , '数学' , 83)
insert into #T2 values('张三' , '物理' , 93)
insert into #T2 values(NULL , '数学' , 50)
--drop table #t,#T2
go
select
*
from
#T a
cross apply
(select 课程,分数 from #t2 where 姓名=a.姓名) b
/*
姓名 课程 分数
---------- ---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
(3 行受影响)
*/
select
*
from
#T a
outer apply
(select 课程,分数 from #t2 where 姓名=a.姓名) b
/*
姓名 课程 分数
---------- ---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
李四 NULL NULL
NULL NULL NULL
(5 行受影响)
*/
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 的字段， 为啥不能这样做呢？我猜可能微软当时没有加这个功能：），后来有客户抱怨后， 于是微软就增加了 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 仍会包含这一行数据，这就是它和 cross join 的不同之处。
下面是完整的测试代码，你可以在 SQL Server 2005 联机帮助上找到：
-- 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
)
go
-- create Departments table and insert values
create table Departments
(
deptid int not null primary key,
deptname varchar(25) not null
)
go
-- fill datas
insert 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 function
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
-- 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
