sql - 什么时候应该在内部联接上使用交叉应用？(When should I use cross apply over inner join?)

Question

What is the main purpose of using CROSS APPLY ? (使用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. (我已经读过（模糊地通过Internet上的帖子），如果您正在分区，则在选择大型数据集时， cross apply会更有效。) (Paging comes to mind) (（想起分页）)

I also know that CROSS APPLY doesn't require a UDF as the right-table. (我也知道， CROSS APPLY不需要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. (在大多数INNER JOIN查询（一对多关系）中，我可以重写它们以使用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? (在CROSS APPLY在INNER JOIN也能正常工作的情况下， CROSS APPLY时，谁能给我一个很好的例子？)

---

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) (（向我展示它们的不同之处和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

  ask by Jeff Meatball Yang translate from so

Answer 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? (在CROSS APPLY在INNER JOIN也能正常工作的情况下，CROSS APPLY有所作为时，谁能给我一个很好的例子？)

See the article in my blog for detailed performance comparison: (有关性能比较的详细信息，请参阅我博客中的文章：)

• INNER JOIN vs. CROSS APPLY (INNER JOIN与CROSS APPLY)

CROSS APPLY works better on things that have no simple JOIN condition. (在没有简单的JOIN条件的情况下， CROSS APPLY效果更好。)

This one selects 3 last records from t2 for each record from t1 : (这个从t2为t1每个记录选择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

It cannot be easily formulated with an INNER JOIN condition. (在INNER JOIN条件下不容易配制。)

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是一个包含约20,000,000条记录的表，其id为PRIMARY KEY 。)

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. (是即时的。)