我应该什么时候使用交叉应用于内部连接?

What is the main purpose of using CROSS APPLY?

使用交叉应用的主要目的是什么?

I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind)

我已经读过(模糊地，通过互联网上的帖子)，如果您是分区，那么在选择大型数据集时，交叉应用会更有效。(分页)

I also know that CROSS APPLY doesn't require a UDF as the right-table.

我也知道交叉应用不需要UDF作为右表。

In most INNER JOIN queries (one-to-many relationships), I could rewrite them to use CROSS APPLY, but they always give me equivalent execution plans.

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

Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?

谁能给我一个很好的例子来说明交叉应用在那些内部连接也能起作用的情况下会有什么不同?

---

Edit:

编辑:

Here's a trivial example, where the execution plans are exactly the same. (Show me one where they differ and where cross apply is faster/more efficient)

这里有一个简单的例子，执行计划是完全相同的。(让我看看他们的不同之处，交叉应用的速度更快/更有效率)

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

11 个解决方案

#1

Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?

谁能给我一个很好的例子来说明交叉应用在那些内部连接也能起作用的情况下会有什么不同?

See the article in my blog for detailed performance comparison:

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

• INNER JOIN vs. CROSS APPLY
• 内连接与交叉应用。

CROSS APPLY works better on things that have no simple JOIN condition.

在没有简单连接条件的情况下，交叉应用会更好。

This one selects 3 last records from t2 for each record from t1:

这一项从t2中选取了3个记录，每条记录t1:

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

It cannot be easily formulated with an INNER JOIN condition.

它不能很容易地用内部联接条件来表述。

You could probably do something like that using CTE's and window function:

你可以用CTE和window函数做一些类似的事情:

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

, but this is less readable and probably less efficient.

但是，这种方法可读性更低，而且效率可能更低。

Update:

更新:

Just checked.

只是检查。

master is a table of about 20,000,000 records with a PRIMARY KEY on id.

master是一个大约2000万条记录的表，其中主键为id。

This query:

这个查询:

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

runs for almost 30 seconds, while this one:

跑了将近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

is instant.

是即时的。

#2

cross apply sometimes enables you to do things that you cannot do with inner join.

交叉应用有时能让你做一些你不能做的事情。

Example (a syntax error):

示例(语法错误):

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

This is a syntax error, because, when used with inner join, table functions can only take variables or constants as parameters. (I.e., the table function parameter cannot depend on another table's column.)

这是一个语法错误，因为当与内部连接一起使用时，表函数只能将变量或常量作为参数。(即。，表函数参数不能依赖于另一个表的列。

However:

然而:

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

This is legal.

这是合法的。

Edit: Or alternatively, shorter syntax: (by ErikE)

编辑:或者，更短的语法:(by ErikE)

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

Edit:

编辑:

Note: Informix 12.10 xC2+ has Lateral Derived Tables and Postgresql (9.3+) has Lateral Subqueries which can be used to a similar effect.

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

#3

Consider you have two tables.

假设有两个表。

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

There are many situations where we need to replace INNER JOIN with CROSS APPLY.

在许多情况下，我们需要将内部连接替换为交叉应用。

1. Join two tables based on TOP n results

1。根据前n个结果加入两个表。

Consider if we need to select Id and Name from Master and last two dates for each Id from Details table.

考虑我们是否需要从Master中选择Id和Name，以及从Details表中选择每个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
• SQL小提琴

The above query generates the following result.

上面的查询生成如下结果。

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

See, it generated results for last two dates with last two date's Id and then joined these records only in the outer query on Id, which is wrong. To accomplish this, we need to use CROSS APPLY.

查看，它生成了最后两个日期的结果，最后两个日期的Id，然后只在Id的外部查询中加入这些记录，这是错误的。要做到这一点，我们需要使用交叉应用。

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
• SQL小提琴

and forms the following result.

并形成如下结果。

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

Here's how it works. The query inside CROSS APPLY can reference the outer table, where INNER JOIN cannot do this (it throws compile error). When finding the last two dates, joining is done inside CROSS APPLY i.e., WHERE M.ID=D.ID.

这是它是如何工作的。交叉应用中的查询可以引用外部表，内部连接不能这样做(它会抛出编译错误)。当发现最后两个日期时，加入是在交叉应用中。,M.ID = D.ID。

2. When we need INNER JOIN functionality using functions.

2。当我们需要使用函数的内部连接功能时。

CROSS APPLY can be used as a replacement with INNER JOIN when we need to get result from Master table and a function.

当我们需要从主表和函数获得结果时，交叉应用可以作为内部连接的替换。

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

And here is the function

这是函数。

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

• SQL FIDDLE
• SQL小提琴

which generated the following result

哪个产生了以下结果?

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

ADDITIONAL ADVANTAGE OF CROSS APPLY

交叉应用的额外好处。

APPLY can be used as a replacement for UNPIVOT. Either CROSS APPLY or OUTER APPLY can be used here, which are interchangeable.

应用可作为非枢轴的替代。在这里可以使用交叉应用或外部应用，这是可以互换的。

Consider you have the below table(named MYTABLE).

请考虑下面的表(命名为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

The query is below.

下面的查询。

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

• SQL FIDDLE
• SQL小提琴

which brings you the result

这给你带来了什么结果?

  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

#4

here is an example when CROSS APPLY makes a huge difference with performance:

这里有一个例子，交叉应用会带来巨大的性能差异:

Using CROSS APPLY to optimize joins on BETWEEN conditions

使用交叉应用在条件之间优化连接。

Note that besides replacing inner joins you can also reuse code such as truncating dates without paying performance penalty for involing scalar UDFs, for example: Calculating third Wednesday of the month with inline UDFs

注意，除了替换内部连接之外，您还可以重用代码，比如截断日期，而不需要支付涉及标量udf的性能惩罚，例如:用内联udf计算月的第三个星期三。

#5

It seems to me that CROSS APPLY can fill a certain gap when working with calculated fields in complex/nested queries, and make them simpler and more readable.

在我看来，跨应用程序在处理复杂/嵌套查询中的计算字段时可以填补一定的空白，使它们更简单、更可读。

Simple example: you have a DoB and you want to present multiple age-related fields that will also rely on other data sources (such as employment), like Age, AgeGroup, AgeAtHiring, MinimumRetirementDate, etc. for use in your end-user application (Excel PivotTables, for example).

简单的例子:您有一个DoB，您想要呈现多个与年龄相关的字段，这些字段还将依赖于其他数据源(例如雇佣)，比如年龄、年龄、年龄、年龄、最小退休日期等，以便在最终用户应用程序中使用(例如，Excel数据透视表)。

Options are limited and rarely elegant:

选择是有限的，很少是优雅的:

• JOIN subqueries cannot introduce new values in the dataset based on data in the parent query (it must stand on its own).

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

• UDFs are neat, but slow as they tend to prevent parallel operations. And being a separate entity can be a good (less code) or a bad (where is the code) thing.

  udf是整洁的，但是缓慢，因为它们倾向于防止并行操作。作为一个独立的实体可以是一个好的(更少的代码)或者一个坏的(代码)的东西。

• Junction tables. Sometimes they can work, but soon enough you're joining subqueries with tons of UNIONs. Big mess.

  结表。有时他们可以工作，但很快你就会加入无数的工会。大混乱。

• Create yet another single-purpose view, assuming your calculations don't require data obtained mid-way through your main query.

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

• Intermediary tables. Yes... that usually works, and often a good option as they can be indexed and fast, but performance can also drop due to to UPDATE statements not being parallel and not allowing to cascade formulas (reuse results) to update several fields within the same statement. And sometimes you'd just prefer to do things in one pass.

  中间表。是的……这通常是可行的，而且通常是一个不错的选择，因为它们可以被索引和快速，但是性能也会下降，因为更新语句不是并行的，不允许层叠公式(重用结果)在同一语句中更新多个字段。有时候，你更喜欢一次性完成任务。

• Nesting queries. Yes at any point you can put parenthesis on your entire query and use it as a subquery upon which you can manipulate source data and calculated fields alike. But you can only do this so much before it gets ugly. Very ugly.

  嵌套查询。是的，在任何时候，您都可以在整个查询中插入括号，并将其用作子查询，以便您可以操作源数据和计算字段。但是你只能在它变丑之前这么做。很丑。

• Repeating code. What is the greatest value of 3 long (CASE...ELSE...END) statements? That's gonna be readable!

  重复代码。3长(CASE…ELSE…END)语句的最大价值是什么?那是会读!

  • Tell your clients to calculate the damn things themselves.
  • 告诉你的客户自己去计算这些该死的东西。

Did I miss something? Probably, so feel free to comment. But hey, CROSS APPLY is like a godsend in such situations: you just add a simple CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl and voilà! Your new field is now ready for use practically like it had always been there in your source data.

我错过什么了吗?很可能，所以请随意评论。但是，在这种情况下，交叉应用就像天赐之物:你只需添加一个简单的交叉应用(选择tbl)。价值+ 1作为某种公式)作为横线，瞧!您的新字段现在已经准备好了，实际上就像它一直存在于您的源数据中一样。

Values introduced through CROSS APPLY can...

通过交叉应用引入的数值可以……

• be used to create one or multiple calculated fields without adding performance, complexity or readability issues to the mix
• 用于创建一个或多个计算字段，而不添加性能、复杂性或可读性问题。
• like with JOINs, several subsequent CROSS APPLY statements can refer to themselves: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
• 与连接一样，后续的几个交叉应用语句可以引用它们自己:交叉应用(选择crossTbl)。(某公式+ 1)作为横坐标。
• you can use values introduced by a CROSS APPLY in subsequent JOIN conditions
• 您可以在随后的连接条件中使用交叉应用的值。
• As a bonus, there's the Table-valued function aspect
• 作为奖励，有表值函数方面。

Dang, there's nothing they can't do!

该死，他们什么也做不了!

#6

Cross apply works well with an XML field as well. If you wish to select node values in combination with other fields.

交叉应用也适用于XML字段。如果您希望选择与其他字段相结合的节点值。

For example, if you have a table containing some xml

例如，如果您有一个包含一些xml的表。

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

Using the query

使用查询

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

Will return a result

将返回一个结果

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

#7

I guess it should be readability ;)

我猜它应该是可读性的;

CROSS APPLY will be somewhat unique for people reading to tell them that a UDF is being used which will be applied to each row from the table on the left.

交叉应用程序在人们阅读的时候会有一些独特之处，告诉他们一个UDF正在被使用，它将被应用到左边的表格中的每一行。

Ofcourse, there are other limitations where a CROSS APPLY is better used than JOIN which other friends have posted above.

当然，还有其他一些限制，比如交叉应用比其他朋友在上面发布的更有用。

#8

Here is an article that explains it all, with their performance difference and usage over JOINS.

这里有一篇文章解释了这一切，它们的性能差异和使用超过了连接。

SQL Server CROSS APPLY and OUTER APPLY over JOINS

SQL Server交叉应用，外部应用于连接。

As suggested in this article, there is no performance difference between them for normal join operations (INNER AND CROSS).

正如本文所建议的，正常连接操作(内部和交叉)之间没有性能差异。

The usage difference arrives when you have to do a query like this:

当您需要进行这样的查询时，使用差异就会到来:

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)

That is, when you have to relate with function. This cannot be done using INNER JOIN, which would give you the error "The multi-part identifier "D.DepartmentID" could not be bound." Here the value is passed to the function as each row is read. Sounds cool to me. :)

也就是说，当你要和函数联系起来的时候。这不能用内连接来完成，这将给您错误的“多部分标识符”D。部门“不能被束缚。”在这里，值被传递给函数，因为每一行都被读取。我听起来很酷。:)

#9

Well I am not sure if this qualifies as a reason to use Cross Apply versus Inner Join, but this query was answered for me in a Forum Post using Cross Apply, so I am not sure if there is an equalivent method using Inner Join:

我不确定这是否可以作为使用Cross Apply和Inner Join的理由，但是这个查询在一个使用Cross Apply的论坛帖子中得到了回答，所以我不确定是否有使用内连接的equalivent方法:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

AS BEGIN

作为开始

-- 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

END

结束

#10

Cross apply can be used to replace subquery's where you need a column of the subquery

交叉应用可以用来替换子查询，在那里您需要一个子查询的列。

subquery

子查询

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

here i won't be able to select the columns of company table so, using cross apply

这里我不能选择公司表的列，所以使用cross apply。

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

#11

This is perhaps an old question, but I still love the power of CROSS APPLY to simplify the re-use of logic and to provide a "chaining" mechanism for results.

这也许是一个老问题，但我仍然喜欢交叉应用的力量，以简化逻辑的重用，并为结果提供一个“链接”机制。

I've provided a SQL Fiddle below which shows a simple example of how you can use CROSS APPLY to perform complex logical operations on your data set without things getting at all messy. It's not hard to extrapolate from here more complex calculations.

下面我提供了一个SQL Fiddle，它展示了一个简单的示例，说明如何使用CROSS应用程序在数据集上执行复杂的逻辑操作，而不会造成混乱。从这里推断出更复杂的计算并不难。

http://sqlfiddle.com/#!3/23862/2

http://sqlfiddle.com/ ! 3/23862/2