sql - outer - 什么时候应该使用交叉套用内部联结?




sql cross apply是什么 (8)

使用CROSS APPLY的主要目的是什么?

我已阅读(隐约地通过互联网上的帖子),如果您正在进行分区,则在选择大型数据集时, cross apply可能会更有效。 (寻呼可想而知)

我也知道CROSS APPLY 不需要UDF作为右表。

在大多数INNER JOIN查询(一对多关系)中,我可以重写它们以使用CROSS APPLY ,但它们总是给我等效的执行计划。

任何人都可以给我一个很好的例子,说明什么时候CROSS APPLYINNER JOIN工作的情况下会INNER JOIN

编辑:

以下是一个简单的例子,执行计划完全相同。 (向我展示一个他们不同的地方, cross apply的地方更快/更高效)

create table Company (
    companyId int identity(1,1)
,   companyName varchar(100)
,   zipcode varchar(10) 
,   constraint PK_Company primary key (companyId)
)
GO

create table Person (
    personId int identity(1,1)
,   personName varchar(100)
,   companyId int
,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
,   constraint PK_Person primary key (personId)
)
GO

insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'


insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3 


/* using CROSS APPLY */
select *
from Person p
cross apply (
    select *
    from Company c
    where p.companyid = c.companyId
) Czip

/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId

任何人都可以给我一个很好的例子,说明什么时候CROSS APPLY在INNER JOIN工作的情况下会有所作为?

有关详细的性能比较,请参阅我博客中的文章:

CROSS APPLY适用于没有简单连接条件的事物。

这个从t1选择3条记录中的最后3条记录:

SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM    t2
        WHERE   t2.t1_id = t1.id
        ORDER BY
                t2.rank DESC
        ) t2o

它不容易用INNER JOIN条件制定。

你可以用CTE和窗口函数做类似的事情:

WITH    t2o AS
        (
        SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
        FROM    t2
        )
SELECT  t1.*, t2o.*
FROM    t1
INNER JOIN
        t2o
ON      t2o.t1_id = t1.id
        AND t2o.rn <= 3

,但是这不太可读,可能效率较低。

更新:

刚刚检查。

master是一个包含idPRIMARY KEY的约20,000,000条记录的表。

这个查询:

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    master
        ),
        t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
JOIN    q
ON      q.rn <= t.id

运行近30秒,而这一个:

WITH    t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
CROSS APPLY
        (
        SELECT  TOP (t.id) m.*
        FROM    master m
        ORDER BY
                id
        ) q

是即时的。


APPLY操作符的本质是允许FROM子句中操作符的左侧和右侧之间的关联。

与JOIN相反,不允许输入之间的相关性。

说到APPLY运算符中的相关性,我的意思是在右边我们可以说:

  • 派生表 - 作为带有别名的相关子查询
  • 一个表值函数 - 一个带参数的概念视图,其中参数可以引用左侧

两者都可以返回多个列和行。


cross apply有时可以让你做一些inner join无法做到的事情。

示例(语法错误):

select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id

这是一个语法错误 ,因为与inner join一起使用时,表函数只能将变量或常量用作参数。 (即,表函数参数不能依赖于另一个表的列。)

然而:

select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id

这是合法的。

编辑:或者,更短的语法:(通过ErikE)

select F.* from sys.objects O  
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id

编辑:

注意:Informix 12.10 xC2 +具有横向派生表 ,Postgresql(9.3+)具有可用于类似效果的横向子查询


在我看来,CROSS APPLY在处理复杂/嵌套查询中的计算字段时可以填补一定的空白,并使它们更简单,更易读。

简单的例子:您有一个DoB,并且您想要呈现多个与年龄相关的字段,这些字段也将依赖于其他数据源(例如就业),如Age,AgeGroup,AgeAtHiring,MinimumRetirementDate等,以用于您的最终用户应用程序(例如Excel数据透视表)。

选项有限,很少优雅:

  • JOIN子查询不能根据父查询中的数据在数据集中引入新值(它必须独立)。

  • UDF是整齐的,但很慢,因为它们往往会阻止并行操作。 作为一个独立的实体可以是好的(少代码)或坏的(代码在哪里)。

  • 连接表。 有时他们可以工作,但很快你就会加入大量UNION的子查询。 大混乱。

  • 创建另一个单一目的视图,假设您的计算不需要通过主查询中途获得的数据。

  • 中介表。 是的......通常起作用,并且通常是一个很好的选择,因为它们可以进行索引并且速度很快,但是由于UPDATE语句不是平行的,并且不允许级联公式(重用结果)来更新同样的声明。 有时候,你只是喜欢一次完成任务。

  • 嵌套查询。 是的,在任何时候,您都可以在整个查询中放置括号,并将其用作可以操作源数据和计算字段的子查询。 但只有在它变得丑陋之前,你才能做到这一点。 十分难看。

  • 重复代码。 3长(CASE ... ELSE ... END)陈述的最大价值是多少? 这将是可读的!

    • 告诉你的客户自己计算该死的东西。

我错过了什么? 可能吧,随时发表评论。 但是,嘿,CROSS APPLY在这种情况下就像是天赐之物:你只需添加一个简单的CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl并且CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl ! 您的新领域现在已经可以使用了,就好像它始终存在于您的源数据中一样。

通过CROSS APPLY引入的值可以...

  • 用于创建一个或多个计算字段,而不会增加组合的性能,复杂性或可读性问题
  • 像JOIN一样,几个后续的CROSS APPLY语句可以引用它们自己: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
  • 您可以在随后的JOIN条件中使用由CROSS APPLY引入的值
  • 作为奖励,还有Table-valued功能方面

当然,他们不能做什么!


我想这应该是可读性;)

对于阅读的人来说,CROSS APPLY会有点独特,告诉他们正在使用一个UDF,它将应用于左边表格的每一行。

当然,还有其他限制,其中CROSS APPLY比其他朋友在上面发布的JOIN更好。


考虑你有两张桌子。

主表

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

细节表

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

有很多情况下我们需要用CROSS APPLY替换INNER JOIN

1.根据TOP n结果加入两个表格

考虑我们是否需要从Master Details table选择IdName ,并从Details table为每个Id选择最后两个日期。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

以上查询生成以下结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

请参阅它使用最后两个日期的Id生成最后两个日期的结果,然后仅在Id的外部查询中加入这些记录,这是错误的。 为了完成这个,我们需要使用CROSS APPLY

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

并形成以下结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

这是它的工作原理。 CROSS APPLY内部的查询可以引用外部表, INNER JOIN不能这样做(它会引发编译错误)。 找到最后两个日期时,加入是在CROSS APPLY内完成的,即WHERE M.ID=D.ID

2.当我们需要使用函数的INNER JOIN功能时。

当我们需要从Master表和一个function获得结果时, CROSS APPLY可以用作INNER JOIN的替换。

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

这是功能

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE [email protected]
)

这产生了以下结果

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

交叉应用的附加优点

APPLY可以用作UNPIVOT的替代品。 CROSS APPLYOUTER APPLY可以在这里使用,它们可以互换。

考虑你有下面的表(名为MYTABLE )。

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   | 
|   3  |     NULL    |    NULL      |
x------x-------------x--------------x

查询如下。

SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

这给你带来了结果

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 | 
  |  3   |    NULL     | 
  x------x-------------x


这里有一篇文章解释了这一切,其性能差异和用法超过JOINS。

SQL Server交叉应用和外部应用通过JOINS

正如本文所建议的那样,对于正常的联接操作(INNER和CROSS),它们之间没有性能差异。

当您必须执行如下查询时,使用差异会到达:

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  
RETURNS TABLE 
AS 
RETURN 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = @DeptID 
   ) 
GO 
SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

也就是说,当你不得不与功能相关时。 这不能使用INNER JOIN来完成,这会给你错误“多部分标识符”D.DepartmentID“无法绑定”。 在读取每行时,这个值被传递给函数。 听起来很酷。 :)





cross-apply