Cross Apply And Outer Apply in SQL Server

Introduction

The APPLY operator allows us to invoke a table-valued function for each row returned by an outer table expression of a query. The APPLY operator allows us to join two table expressions; the right table expression is processed every time for each 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. 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.

Now we create two tables, first is Employee and second Project table.

Employee Table

CREATE TABLE Employee
(
   Emp_Id int PRIMARY KEY,
   Emp_Name [nvarchar](max),
   Manager_Id int,
   Project_Id int
)

Now insert some data into Employee table.



Project Table

CREATETABLE Project
(
   Project_Idint,
   Project_Name [nvarchar](max),
   Department [nvarchar](max)
)

Insert data into project table.



Forms of Apply

SQL Server contain two forms of Apply: CROSS APPLY and OUTER APPLY.

CROSS APPLY

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn’t contain any row of left side table expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN.

Example

INNER JOIN Query:

SELECT * FROM ProjectAS PRO
INNERJOIN
Employee AS EMP
ON
PRO.Project_Id=EMP.Project_Id

CROSS APPLY Query:

 

SELECT * FROM ProjectAS PRO
CROSSAPPLY
(SELECT * FROM Employee AS EMP WHERE PRO.Project_Id=EMP.Project_Id)Tab

Above both query produce same result.



OUTER APPLY

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. OUTER APPLY work as LEFT OUTER JOIN.

Example

LEFT OUTER JOIN Query:

 

 

SELECT*FROM ProjectAS PRO
LEFTOUTERJOIN
Employee AS EMP
ON
PRO.Project_Id=EMP.Project_Id

OUTER APPLY Query:

 

 

SELECT*FROM ProjectAS PRO
OUTERAPPLY
(SELECT*FROM Employee AS EMP WHERE PRO.Project_Id=EMP.Project_Id)Tab

Above both query produce same result.



APPLY with User Define function:

We can perform APPLY operation with a function that may be scalar or table valued function. This function will invoke each row and return result that will be associated with the outer table.

Example 1:

Firstly, create a function.

 

 

CREATEFUNCTIONReturn_Info(@Project_IDint)
RETURNS[nvarchar](max)
AS
BEGIN
DECLARE @Info[nvarchar](max);
SET @Info = (SELECT 'Project Name is= ' + Project.Project_Name + 'Deratment is= ' + Project.DepartmentFROM Project WHEREProject.Project_Id = @Project_ID)
RETURN @Info
END

Above function will return a scalar value that is the combine result of Department column and Project column.

Now we perform APPLY on this function.

 

 

SELECT * FROM dbo.EmployeeAS EMP
CROSSAPPLY(select [dbo].[Return_Info](EMP.Project_Id))Tab(Project_Id)

Output



Example 2:

Firstly, create a table valued function.

 

 

CREATE FUNCTION [dbo].[fun_Return_Info](@Project_IDint)
RETURNS @Tab TABLE
(
   Project_Idint,
   Project_Namenvarchar(max),
   Department nvarchar(max)
)
AS
BEGIN
INSERT INTO @Tab
(
   Project_Id,
   Project_Name,
   Department
)
SELECT*FROM dbo.ProjectAS PRO WHERE PRO.Project_Id>@Project_ID
RETURN
END

Above function return information of all project that that’s Project_Id is greater than given Project_id.

SELECT EMP.Project_Id,COUNT(EMP.Project_Id)AS [Total] FROM dbo.Employee AS EMP
OUTERAPPLY
[fun_Return_Info](EMP.Project_Id)
GROUP BY EMP.Project_Id

Output:



APPLY with TOP Command

 

 

SELECT * FROM
(SELECT PRO.*,EMP.Emp_Name,EMP.Emp_Id,EMP.Manager_Id,ROW_NUMBER()OVER(PartitionByPRO.Project_IdOrderByPRO.Project_Id)as RankFROMdbo.ProjectAS PRO
LEFTOUTERJOIN
dbo.EmployeeAS EMP
ON
PRO.Project_Id=EMP.Project_Id)Tab1
WHERE Tab1.Rank<=2

Output:



Above query return top Employee details for each project. We can perform same operation using APPLY.

 

 

SELECT PRO.*,Tab.*FROMdbo.ProjectAS PRO
OUTERAPPLY(SELECTTOP 2 EMP.Emp_Name,EMP.Emp_Id,EMP.Manager_IdFromdbo.EmployeeAS EMP WHERE EMP.Project_Id=PRO.Project_Id)Tab

 

Above query produce same result as previous query.

 

 

Example 4:

 

DECLARE @Tab TABLE
(
   [State] [nvarchar](max),
   City [nvarchar](max)
)

INSERTINTO @Tab
SELECT'Rajasthan','Alwar,Laipur,Ajmer,Kota'UNIONALL
SELECT'Haryana','Hisar,Jhajjar,Rohtak'UNIONALL
SELECT'Maharaster','Mumbai,Pune'

SELECT*FROM @Tab AS [@TA]

Output:



Suppose we have a table that contain State name and City names but city names are stored in comma separated manner, now we want to split each city name. For this we can use APPLY Method as below.

For this we create a function that split city name and return a table that contain the list of city names.

 

ALTER FUNCTION SplitString(@Input NVARCHAR(MAX))
RETURNS @Output TABLE(City NVARCHAR(1000))
AS
BEGIN
DECLARE @Index int;
SET @Input = @Input + ',';
WHILE(LEN(@Input) > 0)
BEGIN
SET @Index = CHARINDEX(',', @Input);
INSERT INTO @Output(City)
VALUES(SUBSTRING(@Input, 0, @Index))
SET @Input = SUBSTRING(@Input, @Index + 1, LEN(@Input));
END
RETURN
END

Now we use this function to split the city name.

 

 

DECLARE @Tab TABLE
(
[State] [nvarchar](max),
City [nvarchar](max)
)

INSERT INTO @Tab
SELECT'Rajasthan','Alwar,Laipur,Ajmer,Kota'UNIONALL
SELECT'Haryana','Hisar,Jhajjar,Rohtak'UNIONALL
SELECT'Maharaster','Mumbai,Pune'

SELECT T.State,Tab.*FROM @Tab AS T
OUTERAPPLY dbo.SplitString(T.City)Tab

Output:



Thanks for reading the article.