Cross Apply 与 Inner Join 的对抗

最新推荐文章于 2024-09-26 13:14:12 发布

dbLenis

最新推荐文章于 2024-09-26 13:14:12 发布

阅读量1.9k

点赞数

分类专栏： sql server 文章标签： sql Apply Join

本文链接：https://blog.csdn.net/wujiandao/article/details/80189076

版权

sql server 专栏收录该内容

68 篇文章 2 订阅

订阅专栏

Cross Apply 是 SQL Server 2005 新出的特性，微软给出的定义原文是这样：

The MSDN definition is:

“The APPLY operator is similar to the JOIN operator, but the
difference is that the right-hand side operator of APPLY can reference
columns from the left-hand side”.

两者最大的区别在于 apply 可以引用操作符左边表的字段。

我们来详细了解下它与 inner join 的区别和联系。

首先两者的目的是不一样的。

cross apply 在保持外部表( outer table 亦称为 left table ) 所有数据行的基础上，对每一行数据做运算，此类运算可以是表连接聚合，两表部分字段合并或者建立在第二张表特定条件下的筛选（此类运算会减少数据行）等等。将 cross apply 比作是运算符的话，外部表的运算肯定是排第一位的，因此顺序比 Join 严格。

Join 专注于两表的链接，即部分字段的合并。这一步之后才能展开做排序，聚合等计算。Inner Join 的隐含条件筛选动作，有可能会减少原表的数据行大小。此时两表是独立的，运算顺序不需要特别明确。

离开场景谈目的，总是行走于迷茫大雾之中一样，纵然千言万语，说尽其理，但读者依然可能听的是云里雾里。

适合 cross apply 的最佳场景：

1 在原表字段作为表函数参数，以此结果作为第二张连接表的时候

2 在计算排名，并取第二张表 Top N 名次记录的时候

当然在这种场景下，使用 join 也是可以解决问题的。只是算法的复杂度变为了O(n^2). 每一行都要做一次聚合排名，每一次要对第二张表做全表（全索引）扫描和聚合。

我们举例来说明：

假设有三张表，用户表，订单表，以及用户订单关联表。

用户表有10000数据，订单表有3650000数据，而用户订单关联表是将这总订单平分给这些用户（为了方便自动化处理）

CREATE TABLE dbo.DimUser (

UserID INT IDENTITY(1, 1)

,UserName NVARCHAR(200)

)

GO

CREATE TABLE dbo.FctOrderHeader (

OrderID BIGINT IDENTITY(1, 1)

,OrderDate DATETIME

,OrderAmount BIGINT

)

GO

CREATE TABLE dbo.FctOrderUser (

OrderID BIGINT

,UserID INT

)

GO

自动化给这些表赋值，语句就不贴了。有兴趣可以留言，我再贴。或者去知识星球找找。用到的思想是 tally table, 即生成连续数字和日期的技巧。

我们需要新建一个表函数，用来接收用户作为参数，返回用户对应订单的详细信息。

CREATE FUNCTION dbo.GetUserOrder (@UserID INT)

RETURNS TABLE

AS

RETURN (

SELECT ord.OrderID

,ord.OrderDate

,ord.OrderAmount

FROM FctOrderHeader ord

INNER JOIN FctOrderUser usr ON ord.OrderID = usr.OrderID

AND usr.UserID = @UserID

)

最精彩之处在于，我们可以用第一张表里的字段作为限制条件，来将第二张表里的数据筛选出来，并且进行计算，合成到第一张表里，而 Inner join 的第二张表，即 right table 是独立的，并不能接收第一张表的任何字段：

SELECT TOP 10 usr.UserName

,ord.OrderID

,ord.OrderDate

,ord.OrderAmount

FROM DimUser usr

CROSS APPLY (

SELECT TOP 3 OrderID

,OrderDate

,OrderAmount

FROM dbo.GetUserOrder(usr.UserID)

ORDER BY OrderDate DESC

,OrderAmount DESC

) ord

ORDER BY usr.UserName ASC;

Inner join 也可以实现类似的查询，但是性能上会差很多，我测出来是 4s:12s 的区别：

WITH BASE_QUERY

AS (

SELECT ord.OrderID

,ord.OrderDate

,ord.OrderAmount

,usr.UserID

,Row_Number() OVER (

PARTITION BY usr.UserID ORDER BY ord.OrderDate DESC

,ord.OrderAmount DESC

) AS RNK

FROM FctOrderHeader ord

INNER JOIN FctOrderUser usr ON ord.OrderID = usr.OrderID

)



SELECT TOP 10 usr.UserName

,ord.OrderID

,ord.OrderDate

,ord.OrderAmount

FROM DimUser usr

INNER JOIN BASE_QUERY ord ON usr.UserID = ord.UserID

WHERE ord.RNK <= 3

ORDER BY usr.UserName

适合 Inner Join 的最佳场景：

1 两表拼接的时候，主表的数据量远远大于副表的数据量，即预存表数据量级很大的情况下，使用 cross apply, 不能有效的利用第二张表来过滤。

而 Inner join 既能高效利用两表的索引，通过 Join 还能同时缩小条件筛选的命中范围。

用 cross apply 统计总数，查询用时特别长，终其根本原因，表函数内部查询和 Left Table （DimUser）查询，并没有受到 Right Table 的时间限制影响：

SELECT count(*) AS rowseffect

FROM DimUser usr

CROSS APPLY (

SELECT TOP 3 OrderID

,OrderDate

,OrderAmount

FROM dbo.GetUserOrder(usr.UserID)

WHERE OrderDate BETWEEN '2015-01-01'

AND '2015-02-1'

ORDER BY OrderDate DESC

,OrderAmount DESC

) ord;

用 Inner Join 就会快很多，因为 Left Table 的总数，被 Inner Table 时间限制住了

WITH BASE_QUERY

AS (

SELECT ord.OrderID

,ord.OrderDate

,ord.OrderAmount

,usr.UserID

,Row_Number() OVER (

PARTITION BY usr.UserID ORDER BY ord.OrderDate DESC

,ord.OrderAmount DESC

) AS RNK

FROM FctOrderHeader ord

INNER JOIN FctOrderUser usr ON ord.OrderID = usr.OrderID

WHERE ord.OrderDate BETWEEN '2015-01-01'

AND '2015-02-1'

)

SELECT count(*) AS rowseffect

FROM DimUser usr

INNER JOIN BASE_QUERY ord ON usr.UserID = ord.UserID

WHERE ord.RNK <= 3