提问



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


我已经阅读(隐约地,通过互联网上的帖子),如果你正在进行分区,那么cross apply在选择大数据集时会更有效率。 (寻找灵感)


我也知道CROSS APPLY并不需要UDF作为右表。[82]


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


任何人都可以给我一个很好的例子,说明CROSS APPLYINNER 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也会起作用的情况下有所作为吗?



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



  • INNER JOIN vs. CROSS APPLY



CROSS APPLY在没有简单JOIN条件的事情上效果更好。[83]


这个3t1中的每个记录选择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是关于20,000,000记录的表PRIMARY KEY id


这个查询:


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


是即时的。

其它参考1


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+)具有横向子查询,可用于类似的效果。[84] [85]

其它参考2


考虑你有两张桌子。


MASTER TABLE


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


DETAILS TABLE


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中选择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



  • SQL FIDDLE



以上查询生成以下结果。[86]


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



  • SQL FIDDLE



并形成以下结果。[87]


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内完成。 67]]即,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 ID=@Id
)



  • SQL FIDDLE



产生了以下结果[88]


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)



  • SQL FIDDLE



这会给你带来结果[89]


  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

其它参考3


这是一个例子,当CROSS APPLY与性能产生巨大差异时:


使用CROSS APPLY优化BETWEEN条件下的连接[90]


请注意,除了替换内部联接之外,您还可以重用代码,例如截断日期而不会因为使用标量UDF而牺牲性能损失,例如:使用内联UDF计算本月的第三个星期三[91]

其它参考4


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


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


选项有限,很少优雅:



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

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

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

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

  • 中介表。是的......这通常是有效的,并且通常是一个很好的选择,因为它们可以被索引和快速,但由于UPDATE语句不是并行的并且不允许级联公式(重用结果)来更新内部的几个字段,性能也会下降同样的声明。有时你只是喜欢一次性做事。

  • 嵌套查询。是的,您可以在任何时候将括号放在整个查询上,并将其用作子查询,您可以在其上操作源数据和计算字段。但是你只能在丑陋之前这么做。十分难看。

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



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




我错过了什么?也许,请随意发表评论。但是,嘿,CROSS APPLY在这种情况下就像天赐之物:你只需添加一个简单的CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl和vo!您的新字段现在可以使用了,就像您在源数据中一直存在的那样。


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



  • 用于创建一个或多个计算字段,而不会向混合中添加性能,复杂性或可读性问题

  • 与JOINs一样,几个后续的CROSS APPLY语句可以引用自己:CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2

  • 您可以在后续的JOIN条件中使用CROSS APPLY引入的值

  • 作为奖励,有表值函数方面



Dang,他们无能为力!

其它参考5


交叉应用也适用于XML字段。如果要与其他字段一起选择节点值。


例如,如果您有一个包含某些xml的表



<root>
    <subnode1>
       <some_node value="1" />
       <some_node value="2" />
       <some_node value="3" />
       <some_node value="4" />
    </subnode1>
</root>



使用查询


SELECT
       id as [xt_id]
      ,xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value
  ,node_attribute_value = [some_node].value('@value', 'int')
  ,lt.lt_name   
FROM dbo.table_with_xml xt
CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])
LEFT OUTER JOIN dbo.lookup_table lt
ON [some_node].value('@value', 'int') = lt.lt_id


将返回结果


xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1     test1            1                    Benefits
1     test1            4                    FINRPTCOMPANY

其它参考6


我想它应该是可读性;)


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


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

其它参考7


交叉应用可用于替换需要子查询列的子查询


子查询


select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')


在这里,我不能选择公司表的列
所以,使用交叉申请


select P.*,T.CompanyName
from Person p
cross apply (
    select *
    from Company C
    where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T

其它参考8


这篇文章解释了这一切,它们的性能差异和JOINS使用情况。


SQL Server CROSS APPLY和OUTER APPLY over JOINS [92]


正如本文所述,它们之间在正常连接操作(INNER和CROSS)之间没有性能差异。


[93]


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


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无法绑定。 这里传递的值读取每一行的功能。听起来很酷我。 :)

其它参考9


好吧,我不确定这是否有资格作为使用Cross Apply和Inner Join的理由,但是这个查询是在使用Cross Apply的论坛帖子中为我回答的,所以我不确定是否有使用Inner Join的等效方法:


Create PROCEDURE [dbo].[Message_FindHighestMatches]

-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)



开始


-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

Create table  #temp
(
    MessageID         int,
    Subjects          nchar(255),
    SubjectsCount    int
)

Insert into #temp Select MessageID, Subjects, SubjectsCount From Message

Select Top 20 MessageID, Subjects, SubjectsCount,
    (t.cnt * 100)/t3.inputvalues as MatchPercentage

From #temp 

cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1
             join dbo.Split(@TopicalNeighborhood,',') as t2
             on t1.value = t2.value) as t
cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3

Order By MatchPercentage desc

drop table #temp


结束

其它参考10


这可能是一个老问题,但我仍然喜欢CROSS APPLY的强大功能,以简化逻辑的重用并为结果提供链接机制。


我在下面提供了一个SQL小提琴,其中显示了一个简单的例子,说明如何使用CROSS APPLY对数据集执行复杂的逻辑操作,而不会让事情变得一团糟。从这里推算更复杂的计算并不难。


http://sqlfiddle.com/#!3/23862/2[94]

其它参考11


APPLY运算符的本质是允许FROM子句中运算符的左侧和右侧之间的相关性。


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


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



  • 派生表 - 作为带别名的相关子查询

  • 表值函数 - 带参数的概念视图,其中参数可以引用左侧



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

其它参考12


这已经在技术上得到了很好的回答,但让我举一个具体的例子说明它是如何非常有用的:


假设您有两个表,Customer和Order。客户有很多订单。


我想创建一个视图,向我提供有关客户的详细信息,以及他们所做的最新订单。仅使用JOINS,这将需要一些自我连接和聚合,这不是很好。但是使用Cross Apply,它非常简单:


SELECT *
FROM Customer
CROSS APPLY (
  SELECT TOP 1 *
  FROM Order
  WHERE Order.CustomerId = Customer.CustomerId
  ORDER BY OrderDate DESC
) T