CODEWITHSUNDEEP
sqlsql-servert-sqlinner-joincross-apply
Mohamed Azizi
Mohamed Azizi

Reputation: 162

Inner Join VS CrossApply

I'm trying to get the same result using two different methods but it appears that I'm doing wrong somewhere and I can't figure it out

first Query with innerjoin:

with cte as(
select *, ROW_NUMBER() OVER(ORDER BY businessentityid desc) AS Row#
from HumanResources.Employee
)
Select A.firstname, B.jobtitle
from Person.Person A
inner join cte B on A.BusinessEntityID = B.BusinessEntityID and B.Row# <= 3

Second Query with Cross Apply:

Select A.firstname, cte.jobtitle 
from Person.Person A
cross apply 
(
Select top 3 * 
from HumanResources.Employee B
where B.BusinessEntityID= A.BusinessEntityID
order by businessentityid DESC
) cte

Am getting two different results, any ideas ??

Update :

Thanks to @PriyankJ who gave me the idea,I managed to find the correct query thus the desired result :

SELECT  **top 3** cte.FirstName ,B.JobTitle
FROM    **HumanResources.Employee B**
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM     **person.person A**
        WHERE   A.BusinessEntityID = B.BusinessEntityID
        ORDER BY
                A.BusinessEntityID DESC
        ) cte

the changes(difference between the first and second query) are highlighted :)

Upvotes: 1

Views: 160

Answers (4)

KumarHarsh
KumarHarsh

Reputation: 5094

Most important thing is why do you want to use CROSS APPLY or INNER JOIN ?

you can try in this manner,

Select A.firstname, cte.jobtitle 
from Person.Person A
cross apply 
(
Select * from
(
Select top 3 * 
from HumanResources.Employee B
order by businessentityid DESC
)B
where B.BusinessEntityID= A.BusinessEntityID
) cte

Now you can easily understand why you were getting different result .

Upvotes: 0

Ryan B.
Ryan B.

Reputation: 3665

So you asked 'why.' This may help.

It's important to understand better what JOIN and CROSS APPLY are really doing.

Consider this schema:

DECLARE @Person as Table (BusinessEntityID int, firstname varchar(10))
DECLARE @Employee as Table (BusinessEntityID int, jobtitle varchar(10))

INSERT INTO @Person (BusinessEntityID, firstname) Values
(1,'Annie'), (2,'Brad'), (3,'Coraline'),  (4,'David')

INSERT INTO @Employee (BusinessEntityID, jobtitle) Values
(1,'Director'), (2,'Manager'), (3,'Analyst'), (4,'Gopher')

And your first query

with cte as(
select *, ROW_NUMBER() OVER(ORDER BY businessentityid desc) AS Row#
from @Employee
)
Select A.firstname, B.jobtitle
from @Person A
inner join cte B on A.BusinessEntityID = B.BusinessEntityID and B.Row# <= 3

Which returns this:

firstname  jobtitle
---------- ----------
Brad       Manager
Coraline   Analyst
David      Gopher

Your CTE is being limited to the top 3 records by BusinessEntityID (descending). So Annie isn't in that set. Then we do inner join, which returns all records from both sets that match on the joined column. So, Director from the Employee table falls out, and our final set is 3 records. But what's the cross apply doing?

Select A.firstname, cte.jobtitle
from @Person A
cross apply 
(
Select top 3 * 
from @Employee B
where B.BusinessEntityID= A.BusinessEntityID
order by businessentityid DESC
) cte

firstname  jobtitle
---------- ----------
Annie      Direcor
Brad       Manager
Coraline   Analyst
David      Gopher

This is different. CROSS APPLY doesn't match the two sets on a joined column. Rather, it evaluates the right side for every row on the left side. There's four rows in Person, and for each one, Cross Apply selects the top 3 records from Employee that will match that row on BusinessEntityID (because of the where clause). That's going to give you 4 rows. Let's change our Employee table a little. 

INSERT INTO @Employee (BusinessEntityID, jobtitle) Values 
(1,'Direcor'),(2,'Manager'),(3,'Analyst'),(4,'Gopher'),(4,'Blacksmith')

We've added a Blacksmith with a duplicate Entity id. What do we get?

JOIN LOGIC

firstname  jobtitle
---------- ----------
Coraline   Analyst
David      Gopher
David      Blacksmith

CROSS APPLY LOGIC

firstname  jobtitle
---------- ----------
Annie      Direcor
Brad       Manager
Coraline   Analyst
David      Gopher
David      Blacksmith

We see that in the joined CTE, the duplicated id has pushed one of our smaller id's off the list of top 3 BusinessEntityIds, and David shows up twice where we've joined on the duplicated IDs.

In the cross apply, we have our four rows from Person, and the top three rows from Employee that match each one of those rows from person. In the case of BusinessEntityID = 4, there are two matches and we get both of them.

The most important point is that Cross apply is evaluating the right side in the context of each row on the left. Join just goes and returns the rows that match.

If you want your Cross Join query to imitate the same 'filtering' effect that an inner join can create, then you must put the table that will be limited on the left side of the cross join.

;with cte as(
select *, ROW_NUMBER() OVER(ORDER BY businessentityid desc) AS Row#
from @Employee
)
Select A.firstname, B.jobtitle
from cte B
cross join @Person A 
where A.BusinessEntityID = B.BusinessEntityID and B.Row# <= 3



firstname  jobtitle
---------- ----------
David      Gopher
David      Blacksmith
Coraline   Analyst

Upvotes: 1

MEdwin
MEdwin

Reputation: 2960

what you need is 'outer apply'. Try this:

Select A.firstname, cte.jobtitle 
from Person.Person A
outer apply 
(
Select top 3 * 
    from HumanResources.Employee B
    where B.BusinessEntityID= A.BusinessEntityID
    order by businessentityid DESC
) cte

Upvotes: 0

PriyankJ
PriyankJ

Reputation: 117

I think the cross apply left table needs to be HumanResources.Employee to get the same result.

SELECT TOP 3
    cte.firstname
   ,A.jobtitle
FROM HumanResources.Employee A
CROSS APPLY (SELECT
        *
    FROM Person.Person B
    WHERE B.BusinessEntityID = A.BusinessEntityID) cte
ORDER BY cte.BusinessEntityID DESC

Upvotes: 2

Related Questions