当我们在 SQL Server 2012 中进行交叉应用和内部连接时

2021-11-16 00:00:00 join apply tsql sql-server sql-server-2012

我有一个关于 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 JOIN is a link between two sets over a condition
  • an APPLY is 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 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.

区别在于调用 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

相关文章