CROSS APPLY and OUTER APPLY in SQL SERVER

CROSS APPLY and OUTER APPLY in SQL SERVER.

Apply operator in SQL server introduced in version 2005. This operator gives you flexibility to join two table expressions where right table expression is processed every time row by row from the left table expression

Left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set.

There are two types of Apply operators in SQL server:

·         CROSS APPLY

·         OUTER APPLY

CROSS APPLY acts like an INNER JOIN, and OUTER APPLY acts like a LEFT OUTER JOIN.

Let’s understand functionality of Cross Apply and Outer Apply with examples:

Let’s take an example of Cross Apply first to understand it in a better way by comparison with Inner Join query. As we know CROSS APPLY operator acts same as Inner Join here in example we have two tables: Employee and Department

To join Employee Table with Department by Inner Join operator:

NOTE: At the end of this article you will find Sample query to build Employee and Department table for Practice.

SELECT *

FROM Employee As Emp

INNER JOIN Department As Dept  ON Emp.DeptID=Dept.DeptID

Cross Apply to join Employee Table with Department:

SELECT *

FROM Employee as Emp

CROSS APPLY (SELECT * FROM Department As Dept WHERE Emp.DeptID=Dept.DeptID) Dept

Now you will say these both query works same than why we need complex code of Corss Apply whenever we have Inner Join as simple so let’s take one scenario as:

Suppose if want to see Top 2 salary for each Department where Salary is in Empoloyee table and Department we have in Department Table.

With Cross Apply, by writing Simple Query we can achieve result as:

Select Dept.DeptName, Emp.Salary from #Dept as Dept

CROSS APPLY (SELECT Top 3 * FROM #Employee As Emp WHERE Emp.DeptID=Dept.DeptID) Emp

Order By Dept.DeptName,Emp.Salary

Result will be as:

DeptName	Salary
IT Analytics	19000
IT Analytics	20000
IT Analytics	35000
IT Apps	15000
IT Apps	18000
IT Apps	26000

 

Now we look at below query without CROSS APPLY, where we have to use Rank or Row Number function to achieve the same result:

SELECT Sal.DeptName,Sal.Salary FROM (

  SELECT Dept.DeptName,Emp.Salary , ROW_NUMBER() OVER (PARTITION BY Dept.DeptName ORDER BY Emp.Salary Desc) RN 

  FROM #Employee as Emp

  INNER JOIN #Dept As Dept ON Emp.DeptID=Dept.DeptID

) Sal

WHERE Sal.RN <= 3

Benefits of Using CROSS APPLY:

We can use TOP operator with CROSS APPLY where it will return TOP N rows for each matching row and no need to use partitioning for using Rank or Row number to short.

OUTER APPLY

OUTER APPLY is similar to Left Join in SQL Server as OUTER APPLY operator in SQL Server returns all rows from the LEFT table expression.

Syntax for OUTER APPLY as:

Simple Query with Left Join:

 

SELECT *

FROM #Employee As Emp

LEFT JOIN #Dept As Dept  ON Emp.DeptID=Dept.DeptID

 

Outer Apply Logic to achieve same result as Left join

 

SELECT *

FROM #Employee as Emp

OUTER APPLY (SELECT * FROM #Dept As Dept WHERE Emp.DeptID=Dept.DeptID) Dept

Drawback of Using Apply Operator:

As apply operator are process row by row so it will be slower than Join.

Sample Data query for Employee and Department Table:

Create Table #Employee

(

EmpID int,

FirstName varchar(20),

LastName varchar(30),

Gender varchar(10),

Salary int,

DeptID int,

ManagerID int

)

 

Insert into #Employee Values (11,'Sonu','Kumar','M',10000,113,16)

Insert into #Employee Values (12,'Ritu','Singh','F',15000,112,15)

Insert into #Employee Values (13,'Parul','Sharma','F',12000,113,16)

Insert into #Employee Values (14,'Sanjay','Kumar','M',18000,112,15)

Insert into #Employee Values (15,'Ajay','Singh','M',20000,114,24)

Insert into #Employee Values (16,'Vijay','Sehgal','M',26000,114,24)

Insert into #Employee Values (17,'Kishor','Kullar','M',19000,115,28)

Insert into #Employee Values (18,'Pramod','Jha','M',26000,112,15)

Insert into #Employee Values (19,'Jai','Kumar','M',15000,113,16)

Insert into #Employee Values (20,'Ram','Singh','M',12000,114,24)

Insert into #Employee Values (21,'Sunil','Verma','M',18000,113,16)

Insert into #Employee Values (22,'Maynak','singh','M',20000,115,28)

Insert into #Employee Values (23,'Prabhu','Sharma','M',26000,112,15)

Insert into #Employee Values (24,'Ankita','Kumar','F',35000,115,28)

Insert into #Employee Values (25,'Anu','Singh','F',19000,112,15)

Insert into #Employee Values (26,'Pooja','Verma','F',26000,115,28)

Insert into #Employee Values (27,'Kamal','Singh','M',31000,112,15)

Insert into #Employee Values (28,'Sunita','Rajpal','F',42000,115,28)

Insert into #Employee Values (29,'Kritika','Kalra','F',18000,114,24)

Insert into #Employee Values (30,'Shilpa','Tripathi','F',20000,114,24)

 

 

Create Table #Dept

(

DeptID int,

DeptName varchar(30)

)

 

Insert into #Dept Values (112,'IT Apps')

Insert into #Dept Values (113,'IT Testing')

Insert into #Dept Values (114,'IT Mainframe')

Insert into #Dept Values (115,'IT Analytics')

 

Select * from #Employee

 

Select * from #Dept