什么时候应该使用Cross Apply over Inner Join？-Java 学习之路

797

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

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

我也知道 CROSS APPLY doesn't require a UDF as the right-table.

在大多数 INNER JOIN 查询（一对多关系）中，我可以将它们重写为使用 CROSS APPLY ，但它们总是给我相同的执行计划 .

任何人都可以给我一个很好的例子，说明 CROSS APPLY 何时在 INNER JOIN 也会起作用的情况下有所作为？

Edit:

这是一个简单的例子，执行计划完全相同 . （告诉我一个他们不同的地方， 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

13 回答

2

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

Using CROSS APPLY to optimize joins on BETWEEN conditions

请注意，除了替换内部联接之外，您还可以重用代码，例如截断日期，而不会因为使用标量UDF而牺牲性能损失，例如：Calculating third Wednesday of the month with inline UDFs

回复于 2024-04-19T06:33:14+08:00
35
在我看来，在使用复杂/嵌套查询中的计算字段时，CROSS APPLY可以填补一定的空白，并使它们更简单，更易读 .

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

选项有限，很少优雅：
- JOIN子查询无法根据父查询中的数据在数据集中引入新值（它必须独立存在） .
- UDF很整洁，但速度慢，因为它们往往会阻止并行操作 . 作为一个单独的实体可以是一个好的（更少的代码）或一个坏的（代码在哪里）的东西 .
- 连接表 . 有时他们可以工作，但很快就会加入大量UNION的子查询 . 大混乱 .
- 创建另一个单用途视图，假设您的计算不需要在主查询中途获得的数据 .
- 中介表 . 是的......这通常是有效的，并且通常是一个很好的选择，因为它们可以被索引和快速，但由于UPDATE语句不是并行的并且不允许级联公式（重用结果）来更新内部的几个字段，性能也会下降同样的声明 . 有时你只是喜欢一次性做事 .
- 嵌套查询 . 是的，您可以在任何时候将括号放在整个查询上，并将其用作子查询，您可以在其上操作源数据和计算字段 . 但是你只能在丑陋之前这么做 . 十分难看 .
- 重复代码 . 3长（CASE ... ELSE ... END）陈述的最大 Value 是什么？那将是可读的！
- 告诉你的客户自己计算该死的东西 .
我错过了什么？也许，请随意发表评论 . 但是，嘿，CROSS APPLY在这种情况下就像天赐之物：你只需添加一个简单的 CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl 和il！您的新字段现在可以使用了，就像您在源数据中一直存在的那样 .

通过CROSS APPLY引入的 Value 可以......
- 用于创建一个或多个计算字段，而不会在混合中添加性能，复杂性或可读性问题
- 与JOIN类似，几个后续的CROSS APPLY语句可以引用自己： CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
- 您可以在后续JOIN条件中使用CROSS APPLY引入的值
- 作为奖励，有表值函数方面
Dang，他们没有什么不能做的！
回复于 2024-04-19T06:33:14+08:00
129

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

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

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

回复于 2024-04-19T06:33:14+08:00

591

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

子查询

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

回复于 2024-04-19T06:33:14+08:00

5
cross apply 有时会让你做一些你无法用_86827做的事情 .

示例（语法错误）：
```
select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id
```
这是 syntax error ，因为当与 inner join 一起使用时，表函数只能将变量或常量作为参数 . （即，表函数参数不能依赖于另一个表的列 . ）

然而：
```
select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id
```
这是合法的 .

Edit: 或者，更短的语法:(由ErikE提供）
```
select F.* from sys.objects O  
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id
```
Edit:

注意：Informix 12.10 xC2具有Lateral Derived Tables，Postgresql（9.3）具有Lateral Subqueries，可用于类似的效果 .
回复于 2024-04-19T06:33:14+08:00

好吧，我不确定这是否有资格作为使用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

结束

回复于 2024-04-19T06:33:14+08:00

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

假设您有两个表，即客户和订单 . 客户有很多订单 .

我想创建一个视图，向我提供有关客户的详细信息，以及他们最近的订单 . 只使用JOINS，这将需要一些自我连接和聚合，这不是很好 . 但是使用Cross Apply，它非常简单：
```
SELECT *
FROM Customer
CROSS APPLY (
  SELECT TOP 1 *
  FROM Order
  WHERE Order.CustomerId = Customer.CustomerId
  ORDER BY OrderDate DESC
) T
```
回复于 2024-04-19T06:33:14+08:00

考虑你有两张 table .

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

在许多情况下，我们需要将 INNER JOIN 替换为 CROSS APPLY .

1. Join two tables based on TOP n results

考虑是否需要从 Master 中选择 Id 和 Name ，并从 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

以上查询生成以下结果 .

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

并形成以下结果 .

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 内完成，即 WHERE M.ID=D.ID .

2. When we need INNER JOIN functionality using functions.

当我们需要从 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

产生了以下结果

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 可以用作 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 FIDDLE

这会带给你结果

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

回复于 2024-04-19T06:33:14+08:00

3

我想它应该是可读性;）

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

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

回复于 2024-04-19T06:33:14+08:00
2
APPLY运算符的本质是允许FROM子句中运算符的左侧和右侧之间的相关性 .

与JOIN相反，不允许输入之间的相关性 .

说到APPLY运算符中的相关性，我的意思是在右侧我们可以放：
- 派生表 - 作为带别名的相关子查询
- 表值函数 - 带参数的概念视图，其中参数可以引用左侧
两者都可以返回多个列和行 .
回复于 2024-04-19T06:33:14+08:00
0
这篇文章解释了这一切，它们的性能差异和JOINS使用情况 .

SQL Server CROSS APPLY and OUTER APPLY over JOINS

正如本文所述，它们之间在正常连接操作（INNER和CROSS）之间没有性能差异 .

当您必须执行以下查询时，使用差异会到达：
```
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来完成，它会给你错误 "The multi-part identifier "D.DepartmentID" could not be bound." 这里的值在读取每一行时传递给函数 . 听起来很酷我 . :)
回复于 2024-04-19T06:33:14+08:00

任何人都可以给我一个很好的例子，说明当CRNER APPLY在INNER JOIN也会起作用的情况下有所作为吗？

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

INNER JOIN vs. CROSS APPLY

CROSS APPLY 在没有简单 JOIN 条件的事情上效果更好 .

这个从 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

它不容易用 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

，但这不太可读，可能效率较低 .

Update:

刚检查过 .

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

运行几乎_86824秒，而这一秒：

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

是即时的 .

回复于 2024-04-19T06:33:14+08:00

180

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

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

<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

回复于 2024-04-19T06:33:14+08:00

什么时候应该使用Cross Apply over Inner Join？

13 回答

相关问题