Cross Apply And Outer Apply in SQL Server

CREATE FUNCTION Return_Info(@Project_IDint)    
RETURNS [nvarchar](max)    
AS    
BEGIN    
DECLARE @Info[nvarchar](max);    
SET @Info = (SELECT 'Project Name is= ' + Project.Project_Name + 'Deratment is= ' + Project.Department FROM Project WHERE Project.Project_Id = @Project_ID)    
RETURN @Info    
END  

The above function will return a scalar value that is the combined result of the Department column and Project column.

Now we perform APPLY on this function.

SELECT * FROM dbo.Employee AS EMP    
CROSS APPLY(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  

The above function return information on all project that’s Project_Id is greater than the given Project_id.

SELECT EMP.Project_Id,COUNT(EMP.Project_Id) AS [Total] FROM dbo.Employee AS EMP    
OUTER APPLY    
[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(Partition By PRO.Project_Id Order By PRO.Project_Id)as Rank FROM dbo.Project AS PRO    
LEFT OUTER JOIN    
dbo.Employee AS 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 the same operation using APPLY.

SELECT PRO.*,Tab.* FROM dbo.Project AS PRO    
OUTER APPLY(SELECTTOP 2 EMP.Emp_Name,EMP.Emp_Id,EMP.Manager_Id Fromd bo.Employee AS EMP WHERE EMP.Project_Id=PRO.Project_Id)Tab    

The above query produces the same result as the previous query.

Example 3

DECLARE @Tab TABLE    
(    
   [State] [nvarchar](max),    
   City [nvarchar](max)    
)    
    
INSERT INTO @Tab    
SELECT 'Rajasthan','Alwar,Laipur,Ajmer,Kota' UNION ALL    
SELECT 'Haryana','Hisar,Jhajjar,Rohtak' UNION ALL    
SELECT 'Maharaster','Mumbai,Pune'    
    
SELECT * FROM @Tab AS [@TA]  

Output

Suppose we have a table that contains State names and City names but city names are stored in comma separate 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 contains 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' UNION ALL    
SELECT 'Haryana','Hisar,Jhajjar,Rohtak' UNION ALL    
SELECT 'Maharaster','Mumbai,Pune'    
    
SELECT T.State,Tab.*FROM @Tab AS T    
OUTERAPPLY dbo.SplitString(T.City)Tab  

Output

Summary

In this article, you will learn about Apply operator in SQL Server and its types with examples. Learn Cross Apply And Outer Apply in SQL Server. Thanks for reading the article.