What is “cross apply” and “outer apply” keyword in SQL Server?


Cross Apply is like inner join which return all rows from left table if second table has corresponding rows. Top keyword works with Cross Apply but not with Inner Join.
Example:

Create table #User
(
iUserId int,
sEmpName varchar(100)
)
Create table #UserGroup
(
iUserId int,
sGroupName varchar(100)
)

Insert into #User
Select 1,'Hitesh' union
select 2,'Priyanka' union
select 3,'Dev' union
select 4,'Neha'

Insert into #UserGroup
select 1,'Agent' union
select 1,'Supervisor' union
select 1,'Admin' union
select 2,'Agent' union
select 3,'Supervisor'

Sql query with “Cross Apply” uses “Top”

select iUserId,sEmpName,UG.sGroupName 
From #User U(Nolock)
cross apply (select top 1 sGroupName from #UserGroup G Where U.iUserId=G.iUserId) UG

The above query will return all users from left table with only one group from second table.

Cross Apply

Cross Apply

The result set does not include “Neha” as the other table does not have any correspondence row.

Outer Apply is like left join which return all rows from left table if second table does or does not have corresponding rows. Top keyword works with Outer Apply but not with left Join.
Sql query with “Outer Apply” uses “Top”

select iUserId,sEmpName,UG.sGroupName 
From #User U(Nolock)
outer apply (select top 1 sGroupName from #UserGroup G Where U.iUserId=G.iUserId) UG

The above query will return all users from left table with only one group from second table.

Outer Apply

Outer Apply

The result set include “Neha” whether second table does not have any correspondence row.