我什么时候应该使用 CROSS APPLY 而不是 INNER JOIN？

谁能给我一个很好的例子，说明何时 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 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

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

更新：

刚检查。

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

是瞬间的。

---

考虑你有两张桌子。

主桌

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

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

<强> 1。根据 TOP n 结果加入两个表

考虑我们是否需要从 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 小提琴

上述查询生成以下结果。

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 的外部查询中加入这些记录，这是错误的。这应该同时返回 Ids 1 和 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 即 WHERE M.ID=D.ID 内完成加入。

<强> 2。当我们需要使用函数的 INNER JOIN 功能时。

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

这产生了以下结果

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 做不到的事情。

示例（语法错误）：

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

这是一个语法错误，因为与 inner join 一起使用时，表函数只能将 变量或常量 作为参数。 （即，表函数参数不能依赖于另一个表的列。）

然而：

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

这是合法的。

编辑：或者，更短的语法：（由 ErikE）

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

编辑：

注意：Informix 12.10 xC2+ 有 Lateral Derived Tables，Postgresql (9.3+) 有 Lateral Subqueries，它们可以用于类似的效果。

---

在我看来，CROSS APPLY 在处理复杂/嵌套查询中的计算字段时可以填补一定的空白，并使它们更简单、更具可读性。

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

选项有限且很少优雅：

JOIN 子查询不能基于父查询中的数据在数据集中引入新值（它必须独立存在）。

UDF 很简洁，但速度很慢，因为它们往往会阻止并行操作。作为一个独立的实体可能是一件好事（更少的代码）或一件坏事（代码在哪里）。

连接表。有时它们可以工作，但很快你就会加入带有大量 UNION 的子查询。大混乱。

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

中间表。是的...这通常有效，并且通常是一个不错的选择，因为它们可以被索引并且速度很快，但是由于 UPDATE 语句不是并行的并且不允许级联公式（重用结果）来更新多个字段，性能也会下降同样的声明。有时你更喜欢一次性完成。

嵌套查询。是的，在任何时候，您都可以在整个查询上加上括号，并将其用作子查询，您可以在该子查询上操作源数据和计算字段等。但是你只能在它变得丑陋之前做到这一点。十分难看。

重复代码。 3 个长 (CASE...ELSE...END) 语句的最大值是多少？这将是可读的！告诉您的客户自己计算该死的东西。

告诉您的客户自己计算该死的东西。

我错过了什么？可能，所以请随时发表评论。但是，嘿，CROSS APPLY 在这种情况下就像天赐之物：您只需添加一个简单的 CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl 就可以了！您的新字段现在几乎可以使用，就像它一直存在于您的源数据中一样。

通过 CROSS APPLY 引入的值可以...

用于创建一个或多个计算字段，而不会增加性能、复杂性或可读性问题

与 JOIN 一样，随后的几个 CROSS APPLY 语句可以引用自己：CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2

您可以在后续的 JOIN 条件中使用 CROSS APPLY 引入的值

作为奖励，还有表值函数方面

天啊，没有什么是他们做不到的！

---

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

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

我想创建一个视图，为我提供有关客户的详细信息以及他们最近下的订单。仅使用 JOINS，这将需要一些不漂亮的自连接和聚合。但是使用 Cross Apply，它超级简单：

SELECT *
FROM Customer
CROSS APPLY (
  SELECT TOP 1 *
  FROM Order
  WHERE Order.CustomerId = Customer.CustomerId
  ORDER BY OrderDate DESC
) T

---

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

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

使用查询

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

---

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

子查询

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

---

这是一个简短的教程，可以保存在 .sql 文件中并在 SSMS 中执行，我为自己编写了该教程，以快速刷新我对 CROSS APPLY 的工作原理以及何时使用它的记忆：

-- Here's the key to understanding CROSS APPLY: despite the totally different name, think of it as being like an advanced 'basic join'.
-- A 'basic join' gives the Cartesian product of the rows in the tables on both sides of the join: all rows on the left joined with all rows on the right.
-- The formal name of this join in SQL is a CROSS JOIN.  You now start to understand why they named the operator CROSS APPLY.

-- Given the following (very) simple tables and data:
CREATE TABLE #TempStrings ([SomeString] [nvarchar](10) NOT NULL);
CREATE TABLE #TempNumbers ([SomeNumber] [int] NOT NULL);
CREATE TABLE #TempNumbers2 ([SomeNumber] [int] NOT NULL);
INSERT INTO #TempStrings VALUES ('111'); INSERT INTO #TempStrings VALUES ('222');
INSERT INTO #TempNumbers VALUES (111); INSERT INTO #TempNumbers VALUES (222);
INSERT INTO #TempNumbers2 VALUES (111); INSERT INTO #TempNumbers2 VALUES (222); INSERT INTO #TempNumbers2 VALUES (222);

-- Basic join is like CROSS APPLY; 2 rows on each side gives us an output of 4 rows, but 2 rows on the left and 0 on the right gives us an output of 0 rows:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Basic join ('CROSS JOIN')
    #TempStrings st, #TempNumbers nbr
    -- Note: this also works:
    --#TempStrings st CROSS JOIN #TempNumbers nbr

-- Basic join can be used to achieve the functionality of INNER JOIN by first generating all row combinations and then whittling them down with a WHERE clause:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Basic join ('CROSS JOIN')
    #TempStrings st, #TempNumbers nbr
WHERE
    st.SomeString = nbr.SomeNumber

-- However, for increased readability, the SQL standard introduced the INNER JOIN ... ON syntax for increased clarity; it brings the columns that two tables are
-- being joined on next to the JOIN clause, rather than having them later on in the WHERE clause.  When multiple tables are being joined together, this makes it
-- much easier to read which columns are being joined on which tables; but make no mistake, the following syntax is *semantically identical* to the above syntax:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Inner join
    #TempStrings st INNER JOIN #TempNumbers nbr ON st.SomeString = nbr.SomeNumber

-- Because CROSS APPLY is generally used with a subquery, the subquery's WHERE clause will appear next to the join clause (CROSS APPLY), much like the aforementioned
-- 'ON' keyword appears next to the INNER JOIN clause.  In this sense, then, CROSS APPLY combined with a subquery that has a WHERE clause is like an INNER JOIN with
-- an ON keyword, but more powerful because it can be used with subqueries (or table-valued functions, where said WHERE clause can be hidden inside the function).
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr

-- CROSS APPLY joins in the same way as a CROSS JOIN, but what is joined can be a subquery or table-valued function.  You'll still get 0 rows of output if
-- there are 0 rows on either side, and in this sense it's like an INNER JOIN:
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr

-- OUTER APPLY is like CROSS APPLY, except that if one side of the join has 0 rows, you'll get the values of the side that has rows, with NULL values for
-- the other side's columns.  In this sense it's like a FULL OUTER JOIN:
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st OUTER APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr

-- One thing CROSS APPLY makes it easy to do is to use a subquery where you would usually have to use GROUP BY with aggregate functions in the SELECT list.
-- In the following example, we can get an aggregate of string values from a second table based on matching one of its columns with a value from the first
-- table - something that would have had to be done in the ON clause of the LEFT JOIN - but because we're now using a subquery thanks to CROSS APPLY, we
-- don't need to worry about GROUP BY in the main query and so we don't have to put all the SELECT values inside an aggregate function like MIN().
SELECT
    st.SomeString, nbr.SomeNumbers
FROM
    #TempStrings st CROSS APPLY (SELECT SomeNumbers = STRING_AGG(tempNbr.SomeNumber, ', ') FROM #TempNumbers2 tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr
-- ^ First the subquery is whittled down with the WHERE clause, then the aggregate function is applied with no GROUP BY clause; this means all rows are
--   grouped into one, and the aggregate function aggregates them all, in this case building a comma-delimited string containing their values.

DROP TABLE #TempStrings;
DROP TABLE #TempNumbers;
DROP TABLE #TempNumbers2;

---

我想它应该是可读性的；）

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

当然，CROSS APPLY 比上面其他朋友发布的 JOIN 更好地使用还有其他限制。

---

这是一篇解释这一切的文章，以及它们在 JOINS 上的性能差异和用法。

SQL Server CROSS APPLY and OUTER APPLY over JOINS

正如本文所建议的，它们对于普通连接操作（INNER 和 CROSS）没有性能差异。

https://i.stack.imgur.com/wf01k.jpg

当您必须执行这样的查询时，使用差异就会出现：

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 完成，这会给您错误“无法绑定多部分标识符“D.DepartmentID”。”在这里，值在读取每一行时传递给函数。对我来说听起来很酷。 :)

---

APPLY 运算符的本质是允许在 FROM 子句中运算符的左侧和右侧进行关联。

与 JOIN 相比，不允许输入之间的相关性。

谈到 APPLY 运算符中的相关性，我的意思是我们可以在右侧放置：

派生表 - 作为具有别名的相关子查询

表值函数 - 带有参数的概念视图，其中参数可以引用左侧

两者都可以返回多列和多行。

---

好吧，我不确定这是否有资格作为使用 Cross Apply 与 Inner Join 的理由，但是在论坛帖子中使用 Cross Apply 为我回答了这个查询，所以我不确定是否有使用 Inner Join 的等价方法：

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

结尾

---

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

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

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

---

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

从 Here 偷来的

---

我们使用 CROSS APPLY 来使用来自另一个（更新请求）表的 JSON 更新一个表——因为我们使用 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. ....

---