`
wangyunlei
  • 浏览: 30751 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

CROSS APPLY和 OUTER APPLY 区别详解

阅读更多

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
IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
empid
INT NOT NULL,
mgrid
INT NULL,
empname
VARCHAR(25) NOT 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(25) NOT 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

2
0
分享到:
评论
发表评论

文章已被作者锁定,不允许评论。

相关推荐

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

    SQL Server中的CROSS APPLY和OUTER APPLY是两种特殊的表运算符,它们在2005版本及以上引入,主要用于处理复杂的数据转换和联接操作。这些运算符的主要区别在于如何处理右表表达式的结果。 CROSS APPLY用于执行内联...

    sql server关键字详解大全(图文)

    下面把sqlserver中cross apply和outer apply关键字具体介绍展示如下: 1.CROSS APPLY 和OUTER APPLY MSDN解释如下(个人理解不是很清晰): 使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值...

    SQLServer 2008 R2中使用Cross apply统计最新数据和最近数据

    此运算符分为两种类型:`CROSS APPLY`和`OUTER APPLY`,它们在处理数据时具有不同的行为。 首先,`APPLY`运算符的基本原理是将一个表(称为外部表)的每一行与一个表值函数的输出相结合。表值函数作为右输入,而...

    SQLServer APPLY表运算符使用介绍

    APPLY运算符分为两种类型:CROSS APPLY和OUTER APPLY,它们在处理数据时有显著的区别。 CROSS APPLY用于将右表表达式的结果应用到左表的每一行,只有当右表的表达式对左表的每一行都有返回值时,才会在结果集中显示...

    SQL中的left outer join,inner join,right outer join用法详解

    SQL 中的左外连接、内连接、右外连接用法详解 SQL 中的连接操作是数据库管理系统中最基本的操作之一,它能够将多个表中的数据结合起来,生成一个新的结果集。本文将对 SQL 中的左外连接、内连接、右外连接进行详细...

    SQL联合查询inner join、outer join和cross join的区别详解

    对于开发使用到数据库的应用,免不了就要使用联合查询,SQL中常用的联合查询有inner join、outer join和cross join;这三者的区别很多人都应该不是很清楚,包括我自己,下面我们一起来看看,如果你使用join连表,...

    sql server 2008中的apply运算符使用方法

    Apply分为Cross Apply和Outer Apply两种使用方式。具体分析如下: 首先先建立两个表StudentList和ScoreInfo。脚本语言如下: 代码如下:create table StudentList(id int Identity(1,1) not null,Name nvarchar(20) ...

    SQL中inner join、outer join和cross join的区别

    根据不同的连接方式,`JOIN`可以分为几种类型,包括`INNER JOIN`、`OUTER JOIN`(左外联接`LEFT JOIN`、右外联接`RIGHT JOIN`以及全外联接`FULL JOIN`)和`CROSS JOIN`。每种类型的`JOIN`都有其独特的应用场景和特点。...

    SQL XML的交叉应用和外部应用之间的区别

    而当我们讨论SQL XML的CROSS APPLY和OUTER APPLY时,我们是在谈论两种特殊的联接方式,它们允许将XML数据与其他表格数据进行复杂的交互。这两者在功能上相似,但存在关键的区别,对于数据库管理员(DBA)来说,理解...

    SQL2005学习笔记 APPLY 运算符

    `APPLY`运算符分为两种类型:`CROSS APPLY`和`OUTER APPLY`。它们的主要区别在于处理没有匹配结果的情况。 1. `CROSS APPLY`: `CROSS APPLY`类似于INNER JOIN,它将左操作数(外部表)的每一行与右操作数(通常是...

    webpack.DefinePlugin与cross-env区别详解

    通过上述对比,我们可以了解到,webpack.DefinePlugin和cross-env虽然都可以用于设置环境变量,但它们的使用场景和目的各有不同。webpack.DefinePlugin更适合在构建过程中定义环境变量,而cross-env更适合在脚本执行...

    inner join-outer join-cross join查询方式总结

    ### 表连接方式详解 在数据库查询中,为了获取跨多个表的数据,通常需要使用到表连接技术。根据连接方式的不同,可以分为内连接(INNER JOIN)、外连接(LEFT JOIN、RIGHT JOIN 和 FULL JOIN)以及交叉连接(CROSS ...

    flash跨域策略文件crossdomain.xml配置详解以及防范措施.docx

    例如,*" headers="SOAPAction,Content-Type" />, 表示允许所有域发送 SOAPAction 和 Content-Type 头部。 0x03 防范措施与最佳实践为了确保站点安全,避免因配置不当引发的跨域安全问题,应遵循以下建议: 1. ...

    cross-env-7.0.3.zip

    《跨平台环境变量工具——cross-env详解》 在软件开发过程中,尤其是在JavaScript的世界里,由于其跨平台的特性,开发者经常需要处理不同操作系统之间的环境差异。`cross-env`是一个非常实用的npm模块,它允许你在...

    使“外部”和“交叉”应用为您服务

    APPLY运算符分为两种类型:CROSS APPLY和OUTER APPLY,它们都是在FROM子句中使用的,与JOIN类似,但提供了更灵活的数据检索方式。理解这两个运算符的差异和使用情境是提升SQL查询效率的关键。 1. **CROSS APPLY**:...

Global site tag (gtag.js) - Google Analytics