CROSS APPLY和 OUTER APPLY 区别详解
来源:互联网 发布:python 写入变量 编辑:程序博客网 时间:2024/05/16 15:40
SQL Server 2005 新增cross apply 和outer apply 联接语句,增加这两个东东有啥作用呢?
我们知道有个 SQL Server 2000 中有个cross join 是用于交叉联接的。实际上增加cross apply 和outer apply 是用于交叉联接表值函数(返回表结果集的函数)的, 更重要的是这个函数的参数是另一个表中的字段。这个解释可能有些含混不请,请看下面的例子:
-- 1. cross join 联接两个表
select *
from TABLE_1as T1
cross join TABLE_2as 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, Line1
The multi-part identifier "T1.column_a" couldnot 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 Departmentsas 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 Departmentsas 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
IF OBJECT_ID('Employees')IS NOTNULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
empid INT NOTNULL,
mgrid INT NULL,
empname VARCHAR(25)NOT NULL,
salary MONEY NOT NULL
)
GO
IF OBJECT_ID('Departments')IS NOTNULL
DROP TABLE Departments
GO
-- create Departments table and insert values
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARYKEY,
deptname VARCHAR(25)NOT NULL,
deptmgrid INT
)
GO
-- fill datas
INSERT INTO employeesVALUES (1,NULL,'Nancy',00.00)
INSERT INTO employeesVALUES (2,1,'Andrew',00.00)
INSERT INTO employeesVALUES (3,1,'Janet',00.00)
INSERT INTO employeesVALUES (4,1,'Margaret',00.00)
INSERT INTO employeesVALUES (5,2,'Steven',00.00)
INSERT INTO employeesVALUES (6,2,'Michael',00.00)
INSERT INTO employeesVALUES (7,3,'Robert',00.00)
INSERT INTO employeesVALUES (8,3,'Laura',00.00)
INSERT INTO employeesVALUES (9,3,'Ann',00.00)
INSERT INTO employeesVALUES (10,4,'Ina',00.00)
INSERT INTO employeesVALUES (11,7,'David',00.00)
INSERT INTO employeesVALUES (12,7,'Ron',00.00)
INSERT INTO employeesVALUES (13,7,'Dan',00.00)
INSERT INTO employeesVALUES (14,11,'James',00.00)
INSERT INTO departmentsVALUES (1,'HR',2)
INSERT INTO departmentsVALUES (2,'Marketing',7)
INSERT INTO departmentsVALUES (3,'Finance',8)
INSERT INTO departmentsVALUES (4,'R&D',9)
INSERT INTO departmentsVALUES (5,'Training',4)
INSERT INTO departmentsVALUES (6,'Gardening',NULL)
GO
--SELECT * FROM departments
-- table-value function
IF OBJECT_ID('fn_getsubtree')IS NOTNULL
DROP FUNCTION fn_getsubtree
GO
CREATE FUNCTION dbo.fn_getsubtree(@empidAS 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 employeesAS e
join employees_subtreeAS es
ON e.mgrid= es.empid
)
SELECT * FROM Employees_Subtree
)
GO
-- cross apply query
SELECT *
FROM DepartmentsAS D
CROSS APPLY fn_getsubtree(D.deptmgrid)AS ST
-- outer apply query
SELECT *
FROM DepartmentsAS D
OUTER APPLY fn_getsubtree(D.deptmgrid)AS ST
我们知道有个 SQL Server 2000 中有个cross join 是用于交叉联接的。实际上增加cross apply 和outer apply 是用于交叉联接表值函数(返回表结果集的函数)的, 更重要的是这个函数的参数是另一个表中的字段。这个解释可能有些含混不请,请看下面的例子:
-- 1. cross join 联接两个表
select *
from TABLE_1as T1
cross join TABLE_2as 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, Line1
The multi-part identifier "T1.column_a" couldnot 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 Departmentsas 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 Departmentsas 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
IF OBJECT_ID('Employees')IS NOTNULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
empid INT NOTNULL,
mgrid INT NULL,
empname VARCHAR(25)NOT NULL,
salary MONEY NOT NULL
)
GO
IF OBJECT_ID('Departments')IS NOTNULL
DROP TABLE Departments
GO
-- create Departments table and insert values
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARYKEY,
deptname VARCHAR(25)NOT NULL,
deptmgrid INT
)
GO
-- fill datas
INSERT INTO employeesVALUES (1,NULL,'Nancy',00.00)
INSERT INTO employeesVALUES (2,1,'Andrew',00.00)
INSERT INTO employeesVALUES (3,1,'Janet',00.00)
INSERT INTO employeesVALUES (4,1,'Margaret',00.00)
INSERT INTO employeesVALUES (5,2,'Steven',00.00)
INSERT INTO employeesVALUES (6,2,'Michael',00.00)
INSERT INTO employeesVALUES (7,3,'Robert',00.00)
INSERT INTO employeesVALUES (8,3,'Laura',00.00)
INSERT INTO employeesVALUES (9,3,'Ann',00.00)
INSERT INTO employeesVALUES (10,4,'Ina',00.00)
INSERT INTO employeesVALUES (11,7,'David',00.00)
INSERT INTO employeesVALUES (12,7,'Ron',00.00)
INSERT INTO employeesVALUES (13,7,'Dan',00.00)
INSERT INTO employeesVALUES (14,11,'James',00.00)
INSERT INTO departmentsVALUES (1,'HR',2)
INSERT INTO departmentsVALUES (2,'Marketing',7)
INSERT INTO departmentsVALUES (3,'Finance',8)
INSERT INTO departmentsVALUES (4,'R&D',9)
INSERT INTO departmentsVALUES (5,'Training',4)
INSERT INTO departmentsVALUES (6,'Gardening',NULL)
GO
--SELECT * FROM departments
-- table-value function
IF OBJECT_ID('fn_getsubtree')IS NOTNULL
DROP FUNCTION fn_getsubtree
GO
CREATE FUNCTION dbo.fn_getsubtree(@empidAS 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 employeesAS e
join employees_subtreeAS es
ON e.mgrid= es.empid
)
SELECT * FROM Employees_Subtree
)
GO
-- cross apply query
SELECT *
FROM DepartmentsAS D
CROSS APPLY fn_getsubtree(D.deptmgrid)AS ST
-- outer apply query
SELECT *
FROM DepartmentsAS D
OUTER APPLY fn_getsubtree(D.deptmgrid)AS ST
- CROSS APPLY和 OUTER APPLY 区别详解
- CROSS APPLY和 OUTER APPLY 区别详解
- CROSS APPLY和 OUTER APPLY 区别详解
- CROSS APPLY和 OUTER APPLY 区别详解
- CROSS APPLY和 OUTER APPLY 区别详解
- CROSS APPLY和 OUTER APPLY 区别详解
- CROSS APPLY和 OUTER APPLY 区别详解
- CROSS APPLY和 OUTER APPLY 区别详解
- CROSS APPLY和 OUTER APPLY 区别详解
- [MSSQL]CROSS APPLY和 OUTER APPLY 区别详解
- cross apply 和 outer apply 的区别
- CROSS APPLY 和outer apply 的区别
- cross apply和outer apply的区别
- cross apply和outer apply的区别
- cross apply 和 outer apply 区别
- cross apply 和 outer apply的区别
- cross apply 和 outer apply
- cross apply 和 outer apply
- Android开发之路
- PHP iconv()函数转字符编码的问题
- 访问iPhone本地的数据库 sqlite3
- 用Perl来帮助生成C++版“自动属性”
- 文章的长短与女人的裙子
- CROSS APPLY和 OUTER APPLY 区别详解
- C# Winform多窗口
- 关于录播教室的几点担心
- LTE from theory to practice
- encodeURIComponent编码后java后台的解码
- C++学习笔记------入门基础
- 推荐一个Android平板高清游戏果冻帝国防御战
- SPring MVC
- XML(2012/2/16)