什么时候我应该在内部连接上使用交叉应用?

使用交叉应用的主要目的是什么?

我读到过(模糊地，通过互联网上的帖子)，如果您正在分区，那么cross apply在选择大型数据集时更有效。(想到寻呼)

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

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

谁能给我举个好例子，说明在那些INNER JOIN也能起作用的情况下，CROSS APPLY会产生影响?

编辑:

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

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 Companyselect 'ABC Company', '19808' unionselect 'XYZ Company', '08534' unionselect '123 Company', '10016'

insert Personselect 'Alan', 1 unionselect 'Bobby', 1 unionselect 'Chris', 1 unionselect 'Xavier', 2 unionselect 'Yoshi', 2 unionselect 'Zambrano', 2 unionselect 'Player 1', 3 unionselect 'Player 2', 3 unionselect 'Player 3', 3

/* using CROSS APPLY */select *from Person pcross apply (select *from Company cwhere p.companyid = c.companyId) Czip
/* the equivalent query using INNER JOIN */select *from Person pinner join Company c on p.companyid = c.companyId
903117 次浏览
小开
最佳答案

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

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

CROSS APPLY在没有简单的JOIN条件的情况下效果更好。

这个选项为t1中的每条记录选择t2中的3最后一条记录:

SELECT  t1.*, t2o.*FROM    t1CROSS APPLY(SELECT  TOP 3 *FROM    t2WHERE   t2.t1_id = t1.idORDER BYt2.rank DESC) t2o

它不能简单地用INNER JOIN条件来表述。

你可以使用CTE's和window函数做类似的事情:

WITH    t2o AS(SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rnFROM    t2)SELECT  t1.*, t2o.*FROM    t1INNER JOINt2oON      t2o.t1_id = t1.idAND t2o.rn <= 3

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

更新:

只是检查。

master是一个包含20,000,000条记录的表，在id上有PRIMARY KEY。

这个查询:

WITH    q AS(SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rnFROM    master),t AS(SELECT  1 AS idUNION ALLSELECT  2)SELECT  *FROM    tJOIN    qON      q.rn <= t.id

运行几乎0秒，而下面这个:

WITH    t AS(SELECT  1 AS idUNION ALLSELECT  2)SELECT  *FROM    tCROSS APPLY(SELECT  TOP (t.id) m.*FROM    master mORDER BYid) q

是即时的。

小开

我想应该是可读性;)

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

当然，还有其他一些限制，CROSS APPLY比JOIN更好用，其他朋友已经在上面发布了。

小开

cross apply有时能让你做inner join做不到的事情。

示例(语法错误):

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

这是语法错误，因为当与inner join一起使用时，表函数只能接受变量或常数作为参数。(也就是说，表函数参数不能依赖于另一个表的列。)

然而:

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

这是合法的。

<强>编辑:或者，更短的语法:(by ErikE)

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

编辑:

< p >注意:Informix 12.10 xC2+有横向衍生表,Postgresql(9.3+)有横向子查询，可以使用类似的效果
小开

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

Create PROCEDURE [dbo].[Message_FindHighestMatches]
-- Declare the Topical Neighborhood@TopicalNeighborhood nchar(255)
< p >开始< / p > 
-- 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 t1join dbo.Split(@TopicalNeighborhood,',') as t2on t1.value = t2.value) as tcross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3
Order By MatchPercentage desc
drop table #temp

结束

小开

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

简单的例子:你有一个DoB，你想要显示多个与年龄相关的字段，这些字段也依赖于其他数据源(比如就业)，比如Age、AgeGroup、AgeAtHiring、MinimumRetirementDate等，以便在最终用户应用程序中使用(例如Excel数据透视表)。

选择是有限的，很少是优雅的:

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

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

  • 结表。有时它们可以工作，但很快就会使用大量的union来连接子查询。大混乱。

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

  • 中间表。是的……这通常是可行的，而且通常是一个很好的选择，因为它们可以快速地建立索引，但由于to UPDATE语句不是并行的，并且不允许级联公式(重用结果)来更新同一语句中的几个字段，因此性能也会下降。有时候你更喜欢一次性完成一些事情。

  • 嵌套查询。是的，在任何时候，您都可以在整个查询上加上圆括号，并将其用作子查询，在该子查询上您可以操作源数据和计算字段。但在事情变糟之前，你也只能这么做了。很丑。

  • < p >重复代码。3个长语句(CASE…ELSE…END)的最大值是多少?这将是可读的!

    • 让你的客户自己计算这些该死的东西。
      • 李< / ul > < / >

    我错过什么了吗?可能吧，请随意评论。但是，嘿，CROSS APPLY在这种情况下就像天赐之物:你只需要添加一个简单的CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl和voilà!您的新字段现在已经可以使用了，就像它一直在源数据中一样。

    通过CROSS APPLY引入的值可以…

    • 用于创建一个或多个计算字段，而不会增加性能、复杂性或可读性问题
    • 像join一样，几个后续的CROSS APPLY语句可以引用自己:CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
    • 您可以在后续的JOIN条件中使用CROSS APPLY引入的值
    • 另外，还有表值函数方面

    该死，没有什么是他们做不到的!

小开

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

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

<root><subnode1><some_node value="1" /><some_node value="2" /><some_node value="3" /><some_node value="4" /></subnode1></root>

使用查询

SELECTid as [xt_id],xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value,node_attribute_value = [some_node].value('@value', 'int'),lt.lt_nameFROM dbo.table_with_xml xtCROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])LEFT OUTER JOIN dbo.lookup_table ltON [some_node].value('@value', 'int') = lt.lt_id

将返回一个结果

xt_id root_attribute_value node_attribute_value lt_name----------------------------------------------------------------------1     test1            1                    Benefits1     test1            4                    FINRPTCOMPANY
小开

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

子查询

select * from person p wherep.companyId in(select c.companyId from company c where c.companyname like '%yyy%')

这里我将无法选择公司表的列因此，使用cross apply

select P.*,T.CompanyNamefrom Person pcross apply (select *from Company Cwhere p.companyid = c.companyId and c.CompanyName like '%yyy%') T
小开

假设您有两张桌子。

掌握表

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 table中为每个Id选择最后两个日期。

SELECT M.ID,M.NAME,D.PERIOD,D.QTYFROM MASTER MINNER JOIN(SELECT TOP 2 ID, PERIOD,QTYFROM DETAILS DORDER BY CAST(PERIOD AS DATE)DESC)DON M.ID=D.ID
  • < a href = " http://sqlfiddle.com/ !3/a633c/1" rel="noreferrer">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.QTYFROM MASTER MCROSS APPLY(SELECT TOP 2 ID, PERIOD,QTYFROM DETAILS DWHERE M.ID=D.IDORDER BY CAST(PERIOD AS DATE)DESC)D
  • < a href = " http://sqlfiddle.com/ !3/a633c/2" rel="noreferrer">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.QTYFROM MASTER MCROSS APPLY dbo.FnGetQty(M.ID) C

这就是函数

CREATE FUNCTION FnGetQty(@Id INT)RETURNS TABLEASRETURN(SELECT ID,PERIOD,QTYFROM DETAILSWHERE ID=@Id)
  • < a href = " http://sqlfiddle.com/ !3/5e731/1" rel="noreferrer">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

# 0

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,DATESFROM MYTABLECROSS APPLY(VALUES (FROMDATE),(TODATE))COLUMNNAMES(DATES)
  • < a href = " http://sqlfiddle.com/ !3/57e22/1" rel="noreferrer">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的强大功能，它简化了逻辑的重用，并为结果提供了一种“链接”机制。

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

< a href = " http://sqlfiddle.com/ !3/23862/2 rel =“nofollow”> http://sqlfiddle.com/ !3/23862/2 < / >

小开

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

SQL Server CROSS APPLY and OUTER APPLY over JOINS

正如本文所建议的，对于正常的连接操作(INNER和CROSS)，它们之间没有性能差异。

enter image description here

当你必须执行这样的查询时，使用差异就出现了:

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)RETURNS TABLEASRETURN(SELECT * FROM Employee EWHERE E.DepartmentID = @DeptID)GO
SELECT * FROM Department DCROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

也就是说，当你必须和函数联系起来的时候。使用INNER JOIN不能做到这一点，这会给您错误"多部分标识符"不能被绑定。"在这里，值是在读取每一行时传递给函数的。听起来很酷。：）

小开

APPLY操作符的本质是允许在FROM子句中操作符的左右侧之间进行关联。

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

说到APPLY运算符中的相关性，我的意思是在右边我们可以写:

  • 派生表——作为具有别名的相关子查询
  • 表值函数——一个带有参数的概念视图，其中参数可以指向左侧

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

小开

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

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

我想创建一个视图，提供关于客户的详细信息，以及他们最近下的订单。对于仅使用join，这将需要一些自连接和聚合，这并不漂亮。但交叉应用，它超级简单:

SELECT *FROM CustomerCROSS APPLY (SELECT TOP 1 *FROM OrderWHERE Order.CustomerId = Customer.CustomerIdORDER BY OrderDate DESC) T
小开

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

从这里

小开

下面是一个简短的教程，可以保存在.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:SELECTst.SomeString, nbr.SomeNumberFROM -- 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:SELECTst.SomeString, nbr.SomeNumberFROM -- Basic join ('CROSS JOIN')#TempStrings st, #TempNumbers nbrWHEREst.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:SELECTst.SomeString, nbr.SomeNumberFROM -- 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).SELECTst.SomeString, nbr.SomeNumberFROM#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:SELECTst.SomeString, nbr.SomeNumberFROM#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:SELECTst.SomeString, nbr.SomeNumberFROM#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().SELECTst.SomeString, nbr.SomeNumbersFROM#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;
小开

我们使用交叉应用从另一个(更新请求)表中使用JSON更新一个表——连接将不起作用，因为我们使用OPENJSON来读取JSON的内容，而OPENJSON是一个“表值函数”。

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

SELECTr.UserRequestId,j.xxxx AS xxxx,FROM  RequestTable as r WITH (NOLOCK)CROSS APPLYOPENJSON(r.JSON, '$.requesttype.recordtype')WITH(r.userrequestid nvarchar(50) '$.userrequestid',j.xxx nvarchar(20) '$.xxx)jWHERE r.Id > @MaxRequestIdand ... etc. ....

提交答案