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
我们知道有个 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