当我们进行交叉申请以及我们在SQL Server 2012中进行内部联接时 [英] When we go for cross apply and when we go for inner join in SQL Server 2012

问题描述

我对SQL Server有一个小问题.什么时候使用cross apply，什么时候使用inner join?为什么在SQL Server中完全使用cross apply?

我有emp，dept表；基于这两个表，我编写了一个inner join和cross apply查询，如下所示:

----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 

两个查询都返回相同的结果.

为什么在SQL Server中需要cross apply?有性能差异吗?你能告诉我吗?

我们什么时候使用cross apply，什么时候使用inner join?这些查询之间是否有性能差异?请告诉我哪种方法是在SQL Server中编写此查询的最佳方法.

解决方案

INNER JOIN和CROSS APPLY(与LEFT JOIN和OUTER APPLY相同)密切相关.在您的示例中，我假设引擎将找到相同的执行计划.

• JOIN是一个条件下两组之间的链接
• APPLY是行子调用

但是-如上所述-优化器非常聪明，并且至少在这种简单情况下-可以理解，归根结底是相同的.

• JOIN将尝试收集子集并在指定条件下链接
• APPLY将尝试反复使用当前行的值调用相关结果.

在使用XML方法.nodes()和更复杂的方案时，调用表值函数(应为内联-语法！)存在差异.

一个如何使用APPLY模拟变量

的示例

...像使用变量一样使用按行计算的结果:

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

这与以下内容相同，但更易于阅读(和键入):

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

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?

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.

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

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 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.

• 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.

• 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.

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

One example how one could use APPLY to simulate variables

...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

One example with XML-method .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);

The result

a1
a2
a3

And one example for an inlined function call

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;

The result

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中进行内部联接时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！