The Difference Between an INNER JOIN and a CROSS APPLY

Friday night football, Wests Tigers are in front and your wife reminds you that you are going to see the in-laws. Sometimes it’s necessary to change plans.

That’s a small example.

But in the wild world of databases there may be queries that are running for an hour that could be bought down to running instantly depending on the plan the database uses.

Consider these two queries:

USE AdventureWorks2022;

GO

--INNER JOIN

SELECT TOP 10 p.businessentityid, p.firstname, lastname, e.SickLeaveHours + VacationHours as attendanceindicator

FROM HumanResources.Employee as e INNER JOIN person.Person as p on p.BusinessEntityID = e.BusinessEntityID

ORDER BY attendanceindicator desc

;

--CROSS APPLY

SELECT p.BusinessEntityID, p.FirstName, p.LastName, theguyswhoneverhaveadayoff

FROM Person.Person as p CROSS APPLY (

SELECT TOP 10 BusinessEntityID, SickLeaveHours + VacationHours as theguyswhoneverhaveadayoff

FROM HumanResources.Employee

ORDER BY (SickLeaveHours + VacationHours) desc

) as X

WHERE X.BusinessEntityID = p.BusinessEntityID

ORDER BY theguyswhoneverhaveadayoff

They are both doing ostensibly the same thing.

When we look at their plans:

INNER JOIN

CROSS APPLY

Different loads are put on different procedures.

For instance if you need to heavily aggregate you may want to sort before selecting.

If you need to query an exceptionally large table, you may want to perform the aggregation on the other table first.

So, friends, if your query is slow it may just be that you need to change the connection condition.

Remember the key to life.

Happy Server that’s a life preserver.

Oh! and Happy wife, happy life.

Don't forget to pay the lady.