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

我已经读到(模糊地,通过互联网上的帖子),如果您正在分区,那么在选择大型数据集时,交叉应用可以更有效。(想到寻呼)

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

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

谁能给我一个很好的例子,CROSS APPLY在那些INNER JOIN也能工作的情况下发挥作用?


编辑:

这里有一个简单的例子,其中执行计划完全相同。(告诉我一个它们的不同之处,交叉应用在哪里更快/更有效)

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

当前回答

当你需要子查询的列时,交叉应用可以用来替换子查询

子查询

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

其他回答

我们使用CROSS APPLY从另一个(更新请求)表中使用JSON更新一个表—join不能用于此,因为我们使用OPENJSON来读取JSON的内容,而OPENJSON是一个“表值函数”。

我本打算在这里放一个简化版的UPDATE命令作为示例,但即使是简化了,作为一个示例,它也太大、太复杂了。因此,这个简单的命令部分“草图”就足够了:

SELECT  
       r.UserRequestId,
       j.xxxx AS xxxx,
FROM  RequestTable as r WITH (NOLOCK)
   CROSS APPLY
      OPENJSON(r.JSON, '$.requesttype.recordtype')
      WITH(
             r.userrequestid nvarchar(50) '$.userrequestid',
             j.xxx nvarchar(20) '$.xxx
           )j
       WHERE r.Id > @MaxRequestId
          and ... etc. ....

假设您有两张桌子。

掌握表

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中选择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小提琴

上述查询生成以下结果。

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上的外部查询中联接这些记录,这是错误的。这应该返回id 1和id 2,但它只返回1,因为1有最后两个日期。为了实现这一点,我们需要使用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小提琴

形成了下面的结果。

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中进行连接,即m.d id = d.d id。

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

当我们需要从主表和函数中获取结果时,可以使用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小提琴

这产生了以下结果

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 APPLY或OUTER 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小提琴

结果是什么

  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

虽然大多数使用CROSS APPLY的查询可以使用INNER JOIN重写,但CROSS APPLY可以产生更好的执行计划和更好的性能,因为它可以在连接发生之前限制正在连接的集合。

从这里偷

好吧,我不确定这是否有资格作为使用交叉应用与内部连接的原因,但这个查询在论坛帖子中使用交叉应用为我回答了,所以我不确定是否有一个等效的方法使用内部连接:

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

END

谁能给我一个很好的例子,CROSS APPLY在那些INNER JOIN也能工作的情况下发挥作用?

查看我博客上的文章,了解详细的性能比较:

内连接与交叉应用

CROSS APPLY在没有简单JOIN条件的情况下工作得更好。

它为t1中的每条记录从t2中选择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连接条件来表述。

你可以使用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

,但可读性较差,可能效率也较低。

更新:

只是检查。

master是一个大约有20,000,000条记录的表,id上有一个PRIMARY KEY。

这个查询:

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

是即时的。