問題描述
我有一個關于 SQL Server 的小問題.什么時候用cross apply，什么時候用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 表；基于這兩個表，我編寫了一個 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
兩個查詢返回相同的結果.
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?
我們什么時候使用cross apply，什么時候使用inner join?這些查詢之間有什么性能差異嗎?請告訴我在 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) 密切相關.在您的示例中，我假設引擎會找到相同的執行計劃.
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是兩個集合之間的一個條件鏈接- 一個
APPLY是一個 row-wise 子調用
- A
JOINis a link between two sets over a condition - an
APPLYis a row-wise sub-call
但是 - 如上所述 - 優化器非常聰明，并且會 - 至少在這種簡單的情況下 - 理解它歸結為相同.
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將嘗試一遍又一遍地使用當前行的值調用相關結果.
- The
JOINwill try to collect the sub-set and link it over the specified condition - The
APPLYwill try to call the related result with the current row's values over and over.
區別在于調用 table-valued-functions(應該是 inline-syntax！)，與 XML 方法 .nodes()以及更復雜的場景.
Differences are in calling table-valued-functions (should be inline-syntax!), with XML-method .nodes() and with more complex scenarios.
...使用按行計算的結果，就像使用變量一樣:
...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
這與以下內容相同，但更易于閱讀(和輸入):
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 方法的一個例子 .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);
結果
a1
a2
a3
一個內聯函數調用的例子
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;
結果
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
這篇關于當我們在 SQL Server 2012 中進行交叉應用和內部連接時的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！