Cross Apply and Outer Apply in SQL Server 2005

In this article I would talk about the New APPLY operator in SQL Server 2005. As usual I would provide a working sample / code snippet for better understanding.

Extract from BOL:

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

Lets see this in action:

Let us create couple of tables and populate with some test data.

Create Table EmployeeMaster
(
EmpId int,
EmpName varchar(50),
Age int,
Grade varchar(5)
)Go

Create Table Loan
(
LoanId int,
EmpId int,
LoanAmount int,
Month varchar(3)
)
Go

Insert Test data:

Insert into EmployeeMaster values (1,'Vadivel',29,'I')
Insert into EmployeeMaster values (2,'Sai',28,'I')
Insert into EmployeeMaster values(3, 'Velias',20, 'II')

Insert into Loan values(1,1,2000,'Jan')
Insert into Loan values(1,2,1000,'Feb')
Insert into Loan values(1,1,1000,'Feb')
Insert into Loan values(1,1,100,'Mar')
Insert into Loan values(1,2,1700,'Jun')
Insert into Loan values(1,1,800,'Aug')

Cross Apply:

Let see how to make use of Cross Apply operator in the below code snippet

Select E.EmpName, A.LoanAmount from EmployeeMaster E
CROSS APPLY
(
Select top 2 LoanId, LoanAmount from Loan L
where L.EmpID=E.EmpId
order by L.LoanAmount desc
) A

Output:

Vadivel 2000
Vadivel 1000
Sai 1700
Sai 1000

Outer Apply:

Let see how to make use of Outer Apply operator in the below code snippet

Select C.EmpName, A.LoanAmount from EmployeeMaster E
OUTER APPLY
(
Select top 2 LoanId, LoanAmount from Loan L
where L.EmpID=E.EmpId
order by L.LoanAmount desc
) A

Output:

Vadivel 2000
Vadivel 1000
Sai 1700
Sai 1000
Velias NULL