当我们进行交叉申请以及我们在SQL Server 2012中进行内部联接时

一个如何使用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 dCROSS APPLY(SELECT CHARINDEX('/',d.SomeString) AS pos1) AS xCROSS 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 @dummyCROSS APPLY SomeXML.nodes(N'/root/a') AS A(All_a_nodes);结果a1a2a3还有一个内联函数调用的示例CREATE FUNCTION dbo.TestProduceRows(@i INT)RETURNS TABLEASRETURN SELECT TOP(@i) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nr FROM master..spt_valuesGOCREATE 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 TestDataCROSS APPLY dbo.TestProduceRows(Number) AS x;GODROP TABLE dbo.TestData;DROP FUNCTION dbo.TestProduceRows;结果1 Show me once 1 12 Show me twice 2 12 Show me twice 2 23 Me five times! 5 13 Me five times! 5 23 Me five times! 5 33 Me five times! 5 43 Me five times! 5 5I 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 applySELECT * 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 conditionan APPLY is a row-wise sub-callBut - 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 conditionThe 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 dCROSS APPLY(SELECT CHARINDEX('/',d.SomeString) AS pos1) AS xCROSS APPLY(SELECT CHARINDEX('/',d.SomeString,x.pos1+1) AS pos2) AS yThis 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 dOne 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 @dummyCROSS APPLY SomeXML.nodes(N'/root/a') AS A(All_a_nodes);The resulta1a2a3And one example for an inlined function callCREATE FUNCTION dbo.TestProduceRows(@i INT)RETURNS TABLEASRETURN SELECT TOP(@i) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nr FROM master..spt_valuesGOCREATE 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 TestDataCROSS APPLY dbo.TestProduceRows(Number) AS x;GODROP TABLE dbo.TestData;DROP FUNCTION dbo.TestProduceRows;The result1 Show me once 1 12 Show me twice 2 12 Show me twice 2 23 Me five times! 5 13 Me five times! 5 23 Me five times! 5 33 Me five times! 5 43 Me five times! 5 5 这篇关于当我们进行交叉申请以及我们在SQL Server 2012中进行内部联接时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！