JOIN and APPLY

CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY
CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY

So you want to improve your SQL skills and have come across some new operators like JOIN and APPLY and are 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 are very similar and
OUTER APPLY and LEFT JOIN are very similar.

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

The only real difference is that CROSS APPLY and OUTER APPLY are better than LEFT and INNER JOIN when you are 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 text 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

CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY
CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY

I hope the above examples help you understand the difference. Leave a comment below if you want to leave a comment.

Leave a Reply

Your email address will not be published. Required fields are marked *