CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY

Like Me on Facebook - https://www.facebook.com/sqlserverdev

So you want to improve your SQL skills and have come across some new operators and wondering when you should use them.

In this blog, I will focus on the following operators in the simplest terms and show you how to use them.

CROSS APPLY and INNER JOIN is very similar and
OUTER APPLY and LEFT JOIN is very similar.

In certain situations, you can write queries using CROSS APPLY or INNER JOIN and using OUTER APPLY and LEFT JOIN and they could bring back the exact results.

The only real difference that CROSS APPLY and OUTER APPLY is better than LEFT and INNER JOIN is when using a function or you want to write an advanced SQL Command to bring back certain results

Run the following script in a SQL Server Environment to see these operators in action.

You can examine the script to see how they differ and you should be able to play around with the script to fully understand what is going on.

create table Company (

    companyId int identity(1,1)

,   companyName varchar(100)

,   zipcode varchar(10) 

,   constraint PK_Company primary key (companyId)

)

GO

create table Person (

    personId int identity(1,1)

,   personName varchar(100)

,   companyId int

,   insertdate DATETIME

,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)

,   constraint PK_Person primary key (personId)

)

GO

insert Company

select 'ABC Company', '19808' union

select 'XYZ Company', '08534' union

select '123 Company', '10016' 

insert Company

select 'No Employees', '11111' 

insert Person

select 'Alan', 1, '20141011 09:00:00' union

select 'Bobby', 1,'20141011 10:00:00' union

select 'Chris', 1, '20141011 11:00:00' union

select 'Xavier', 2, '20141011 12:00:00' union

select 'Yoshi', 2, '20141011 13:00:00' union

select 'Zambrano', 2, '20141011 14:00:00' union

select 'Player 1', 3, '20141011 15:00:00' union

select 'Player 2', 3, '20141011 16:00:00' union

select 'Player 3', 3, '20141011 17:00:00'

/* using CROSS APPLY */

select *

from Person P

cross apply (

    select *

    from Company C

    where p.companyid = c.companyId

) CompanyPerson

/* the equivalent query using INNER JOIN */

select *

from Person p

inner join Company c on p.companyid = c.companyId

/* using CROSS APPLY - brings back the oldest inserted person by company*/

select *

from  Company c 

cross apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

order by insertdate

) CompanyPerson

/* using OUTER APPLY - brings back all the companies and the oldest inserted person by company*/

select *

from  Company c 

outer apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

order by insertdate

) CompanyPerson

order by companyName desc

/*  brings back all the companies and the people */

select *

from company c

left join person p on p.companyid = c.companyId

order by companyName desc

The script produces the following results

Like Me on Facebook - https://www.facebook.com/sqlserverdev