問(wèn)題描述

我有一個(gè)關(guān)于 SQL Server 的小問(wèn)題.什么時(shí)候用cross apply，什么時(shí)候用inner join?為什么在 SQL Server 中完全使用 cross apply?

I have small question about SQL Server. When do we use cross apply, and when do we use inner join? Why use cross apply at all in SQL Server?

我有 emp、dept 表；基于這兩個(gè)表，我編寫了一個(gè) inner join 和 cross apply 查詢，如下所示:

I have emp, dept tables; based on those two tables, I write an inner join and cross apply query like this:

----using cross apply
SELECT * 
FROM Department D 
CROSS APPLY 
    (SELECT * 
     FROM Employee E 
     WHERE E.DepartmentID = D.DepartmentID) A 

----using inner join 
SELECT * 
FROM Department D 
INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID 

兩個(gè)查詢返回相同的結(jié)果.

Both queries return the same result.

這里為什么在 SQL Server 中需要 cross apply ?有性能差異嗎?你能告訴我嗎?

Here why is cross apply needed in SQL Server? Is there performance difference? Can you please tell me?

我們什么時(shí)候使用cross apply，什么時(shí)候使用inner join?這些查詢之間有什么性能差異嗎?請(qǐng)告訴我在 SQL Server 中編寫此查詢的最佳方法是什么.

When will we use cross apply and when inner join? Any performance difference between these queries? Please tell me which is the best way to write this query in SQL Server.

推薦答案

INNER JOIN 和 CROSS APPLY(同 LEFT JOIN 和 OUTER APPLY) 密切相關(guān).在您的示例中，我假設(shè)引擎會(huì)找到相同的執(zhí)行計(jì)劃.

INNER JOIN and CROSS APPLY (same with LEFT JOIN and OUTER APPLY) are very closely related. In your example I'd assume, that the engine will find the same execution plan.

• JOIN 是兩個(gè)集合之間的一個(gè)條件鏈接
• 一個(gè) APPLY 是一個(gè) row-wise 子調(diào)用

• A JOIN is a link between two sets over a condition
• an APPLY is a row-wise sub-call

但是 - 如上所述 - 優(yōu)化器非常聰明，并且會(huì) - 至少在這種簡(jiǎn)單的情況下 - 理解它歸結(jié)為相同.

But - as mentioned above - the optimizer is very smart and will - at least in such easy cases - understand, that it comes down to the same.

• JOIN 將嘗試在指定條件下收集子集并將其鏈接
• APPLY 將嘗試一遍又一遍地使用當(dāng)前行的值調(diào)用相關(guān)結(jié)果.

• The JOIN will try to collect the sub-set and link it over the specified condition
• The APPLY will try to call the related result with the current row's values over and over.

區(qū)別在于調(diào)用 table-valued-functions(應(yīng)該是 inline-syntax！)，與 XML 方法 .nodes()以及更復(fù)雜的場(chǎng)景.

Differences are in calling table-valued-functions (should be inline-syntax!), with XML-method .nodes() and with more complex scenarios.

...使用按行計(jì)算的結(jié)果，就像使用變量一樣:

...to use the result of a row-wise calculation like you'd use a variable:

DECLARE @dummy TABLE(ID INT IDENTITY, SomeString VARCHAR(100));
INSERT INTO @dummy VALUES('Want to split/this at the two/slashes.'),('And/this/also');

SELECT d.ID
      ,d.SomeString
      ,pos1
      ,pos2
      ,LEFT(d.SomeString,pos1-1)
      ,SUBSTRING(d.SomeString,pos1+1,pos2-pos1-1)
      ,SUBSTRING(d.SomeString,pos2+1,1000)
FROM @dummy AS d
CROSS APPLY(SELECT CHARINDEX('/',d.SomeString) AS pos1) AS x
CROSS APPLY(SELECT CHARINDEX('/',d.SomeString,x.pos1+1) AS pos2) AS y

這與以下內(nèi)容相同，但更易于閱讀(和輸入):

This is the same as the following, but much easier to read (and type):

SELECT d.ID
      ,d.SomeString
      ,LEFT(d.SomeString,CHARINDEX('/',d.SomeString)-1)
      ,SUBSTRING(d.SomeString,CHARINDEX('/',d.SomeString)+1,CHARINDEX('/',d.SomeString,(CHARINDEX('/',d.SomeString)+1))-(CHARINDEX('/',d.SomeString)+1))
      ,SUBSTRING(d.SomeString,CHARINDEX('/',d.SomeString,(CHARINDEX('/',d.SomeString)+1))+1,1000)
FROM @dummy AS d

XML 方法的一個(gè)例子 .nodes()

DECLARE @dummy TABLE(SomeXML XML)
INSERT INTO @dummy VALUES
(N'<root>
  <a>a1</a>
  <a>a2</a>
  <a>a3</a>
  <b>Here is b!</b>
</root>');

SELECT All_a_nodes.value(N'.',N'nvarchar(max)')
FROM @dummy
CROSS APPLY SomeXML.nodes(N'/root/a') AS A(All_a_nodes);

結(jié)果

a1
a2
a3

一個(gè)內(nèi)聯(lián)函數(shù)調(diào)用的例子

CREATE FUNCTION dbo.TestProduceRows(@i INT)
RETURNS TABLE
AS
RETURN
    SELECT TOP(@i) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nr FROM master..spt_values
GO

CREATE TABLE dbo.TestData(ID INT IDENTITY, SomeString VARCHAR(100),Number INT);
INSERT INTO dbo.TestData VALUES
 ('Show me once',1)
,('Show me twice',2)
,('Me five times!',5);

SELECT *
FROM TestData
CROSS APPLY dbo.TestProduceRows(Number) AS x;

GO
DROP TABLE dbo.TestData;
DROP FUNCTION dbo.TestProduceRows;

結(jié)果

1   Show me once    1   1
2   Show me twice   2   1
2   Show me twice   2   2
3   Me five times!  5   1
3   Me five times!  5   2
3   Me five times!  5   3
3   Me five times!  5   4
3   Me five times!  5   5

這篇關(guān)于當(dāng)我們?cè)?SQL Server 2012 中進(jìn)行交叉應(yīng)用和內(nèi)部連接時(shí)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！