January 22, 2017 at 3:33 am
Hi Friend, I have small doubt in sql server .
when we go for cross apply and when we go for inner join. why cross apply need in sql server .
I have emp,dept table based on two table I write inner join and cross apply query like below
----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 querys giveng same result.
here why cross apply required in sql server .is there performance diffence.can you please tell me
when will go for cross apply and when will go for inner join and any performance difference between these queries. please tell me the
which is the best way to write query in sql server .
January 23, 2017 at 2:39 am
asrinu13 - Sunday, January 22, 2017 3:33 AMHi Friend, I have small doubt in sql server .
when we go for cross apply and when we go for inner join.
They do very different things.
I suggest you spend some time with the documentation and/or google.
This was the first result from a search: http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2017 at 10:58 am
asrinu13 - Sunday, January 22, 2017 3:33 AMHi Friend, I have small doubt in sql server .
when we go for cross apply and when we go for inner join. why cross apply need in sql server .
I have emp,dept table based on two table I write inner join and cross apply query like below
----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 querys giveng same result.
here why cross apply required in sql server .is there performance diffence.can you please tell me
when will go for cross apply and when will go for inner join and any performance difference between these queries. please tell me the
which is the best way to write query in sql server .
If you are just going to return all of the records, use the INNER JOIN. You want to use a CROSS APPLY in place of INNER JOIN when the right table is dense (has a large number or records) with respect to the reference value(s) from the left table and you are only returning a small portion of those records and the right table has an index that supports the referenced values. We have no idea how dense the right table is with respect to the reference values from the left table or whether there is a supporting index, but returning all of the records does not qualify for the small portion.
There are other uses for CROSS APPLY, but an INNER JOIN simply will not work in those cases.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply