CROSS APPLY和 OUTER APPLY 区别详解

原创 2016年06月01日 15:11:12
SQL Server 2005 新增 cross apply 和 outer apply 联接语句,增加这两个东东有啥作用呢? 

我们知道有个 SQL Server 
2000 中有个 cross join 是用于交叉联接的。实际上增加 crossapply 和 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 
4104Level 16, State 1, Line 1
The multi
-part identifier "T1.column_a" could not be bound.
最后的这个查询的语法有错误。在 
cross join 时,表值函数的参数不能是表 T1 的字段, 为啥不能这样做呢?我猜可能微软当时没有加这个功能:),后来有客户抱怨后, 于是微软就增加了 crossapply 和 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 和 outerapply 的不同之处: 

-- 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) 生成的派生表中没有数据,但 outerapply 仍会包含这一行数据,这就是它和 cross join 的不同之处。 

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

-- create Employees table and insert values
IF OBJECT_ID('Employees'IS NOT NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
empid 
INT NOT NULL,
mgrid 
INT NULL,
empname 
VARCHAR(25NOT NULL,
salary 
MONEY NOT NULL
)
GO
IF OBJECT_ID('Departments'IS NOT NULL
DROP TABLE Departments
GO
-- create Departments table and insert values
CREATE TABLE Departments
(
deptid 
INT NOT NULL PRIMARY KEY,
deptname 
VARCHAR(25NOT NULL,
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
IF OBJECT_ID('fn_getsubtree'IS NOT NULL
DROP FUNCTION  fn_getsubtree
GO
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT
RETURNS TABLE 
AS 
RETURN(
  
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
  )
    
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


下面还有一个小例子,根据小例子可以将cross apply /outer apply 分别换成inner join /left join,结果是一样的,但是cross apply之后一般是带函数.
inner join之后一般是带表.

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

/*
select a.*,b.课程,b.分数
from 
    #T a
inner join #t2 b
where a.姓名=b.姓名

*/
 
/*
姓名         课程         分数
---------- ---------- -----------
张三         语文         74
张三         数学         83
张三         物理         93
 
(3 行受影响)
 
*/
 
select 
    
from 
    #T a
outer apply
    (select 课程,分数 from #t2 where 姓名=a.姓名) b

/*
select a.*,b.课程,b.分数
from 
    #T a
left join #t2 b
where a.姓名=b.姓名

*/

/*
姓名         课程         分数
---------- ---------- -----------
张三         语文         74
张三         数学         83
张三         物理         93
李四         NULL       NULL
NULL       NULL       NULL
 
(5 行受影响)


 

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

cross apply 与 inner join 的区别

下面还有一个小例子,根据小例子可以将cross apply /outer apply 分别换成inner join /left join,结果是一样的,但是cross apply之后一般是带函数. i...

cross apply和表值函数进行关联查询

目前场景是有一个兴趣爱好类型表和一个员工爱好表,员工爱好表中的爱好一列,如果这个员工有多个爱好,那么就将这些爱好放到一条记录当中,爱好之间用逗号隔开,现在需求是要将每个员工的这些爱好分隔成一条条记录查...

SQL关于apply的两种形式cross apply和outer apply

SQL Server数据库操作中,在2005以上的版本新增加了一个APPLY表运算符的功能。新增的APPLY表运算符把右表表达式应用到左表表达式中的每一行。它不像JOIN那样先计算那个表表达式都可以,...

CROSS APPLY和 OUTER APPLY 区别详解

SQL Server 2005 新增 cross apply 和 outer apply 联接语句,增加这两个东东有啥作用呢? 我们知道有个 SQL Server 2000 中有个 c...

SQL Server CROSS APPLY和OUTER APPLY的应用详解

 http://www.jb51.net/article/28666.htm SQL Server数据库操作中,在2005以上的版本新增加了一个APPLY表运算符的功能。新增的APPLY表运算...

SQL Server中CROSS APPLY和OUTER APPLY的应用详解

SQL Server数据库操作中,在2005以上的版本新增加了一个APPLY表运算符的功能。新增的APPLY表运算符把右表表达式应用到左表表达式中的每一行。它不像JOIN那样先计算那个表表达式都可以,...

SQL Server 2005 中 Cross join & Cross Apply & Outer Apply 的区别

SQL Server 2005 中 Cross join & Cross Apply & Outer Apply 的区别 SQL Cross Apply SQL Cross join SQL Out...
  • y_h_t
  • y_h_t
  • 2011-03-21 13:31
  • 1409

T-SQL Part VIII: CROSS APPLY, OUTER APPLY

除了CROSS JOIN, INNER JOIN, OUTER JOIN之外,T-SQL还提供了CROSS APPLY和OUTER APPLY这两个较为另类的Set操作符。首先来看CROSS APPL...

SQL Server 2005 新增 cross apply 和 outer apply 联接语句

我们知道有个 SQL Server 2000 中有个 cross join 是用于交叉联接的。实际上增加 cross apply 和 outer apply 是用于交叉联接表值函数(返回表结果集的函数...

SQL CROSS APPLY OUTER APPLY PIVOT UNPIVOT

set nocount on --> 测试数据:[ta] if object_id('[ta]') is not null drop table [ta] go create ta...
  • Beirut
  • Beirut
  • 2012-04-28 17:46
  • 1083
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)