When we go for cross apply and when we go for inne

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