Cross Apply And Outer Apply in SQL Server

In this article you will learn about Apply operator in SQL Server and its types with example. Learn 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
  1. CREATE TABLE Employee    
  2. (    
  3.    Emp_Id int PRIMARYKEY,    
  4.    Emp_Name [nvarchar](max),    
  5.    Manager_Id int,    
  6.    Project_Id int    
  7. )   
Now insert some data into Employee table.
 
 Employee table 
 
Project Table
  1. CREATE TABLE Project    
  2. (    
  3.    Project_Id int,    
  4.    Project_Name [nvarchar](max),    
  5.    Department [nvarchar](max)    
  6. )  
Insert data into project table.
 
Project table 
 
Forms of Apply
 
SQL Server contain two forms of Apply: CROSS APPLY and OUTER APPLY.
 

CROSS APPLY in SQL Server

 
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. 
 
INNER JOIN Query
  1. SELECT * FROM Project AS PRO     
  2. INNER JOIN    
  3. Employee AS EMP    
  4. ON    
  5. PRO.Project_Id=EMP.Project_Id  
CROSS APPLY Query
  1. SELECT * FROM Project AS PRO     
  2. CROSS APPLY    
  3. (SELECT * FROM Employee AS EMP WHERE PRO.Project_Id=EMP.Project_Id) Tab  
Above both query produce same result.
 
CROSS APPLY in SQL Server 
 

OUTER APPLY in SQL Server

 
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.
 
LEFT OUTER JOIN Query
  1. SELECT * FROM Project AS PRO     
  2. LEFT OUTER JOIN    
  3. Employee AS EMP    
  4. ON    
  5. PRO.Project_Id=EMP.Project_Id  
OUTER APPLY Query
  1. SELECT * FROM Project AS PRO     
  2. OUTER APPLY    
  3. (SELECT*FROM Employee AS EMP WHERE PRO.Project_Id=EMP.Project_Id) Tab   
Above both query produce same result.
 
OUTER APPLY in SQL Server  
 

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.
  1. CREATE FUNCTION Return_Info(@Project_IDint)    
  2. RETURNS [nvarchar](max)    
  3. AS    
  4. BEGIN    
  5. DECLARE @Info[nvarchar](max);    
  6. SET @Info = (SELECT 'Project Name is= ' + Project.Project_Name + 'Deratment is= ' + Project.Department FROM Project WHERE Project.Project_Id = @Project_ID)    
  7. RETURN @Info    
  8. 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.
  1. SELECT * FROM dbo.Employee AS EMP    
  2. CROSS APPLY(select [dbo].[Return_Info](EMP.Project_Id))Tab(Project_Id)    
Output
 
APPLY with User Define function 
 
Example 2
 
Firstly, create a table valued function.
  1. CREATE FUNCTION [dbo].[fun_Return_Info](@Project_IDint)    
  2. RETURNS @Tab TABLE    
  3. (    
  4.    Project_Idint,    
  5.    Project_Namenvarchar(max),    
  6.    Department nvarchar(max)    
  7. )    
  8. AS    
  9. BEGIN    
  10. INSERT INTO @Tab    
  11. (    
  12.    Project_Id,    
  13.    Project_Name,    
  14.    Department    
  15. )    
  16. SELECT*FROM dbo.ProjectAS PRO WHERE PRO.Project_Id>@Project_ID    
  17. RETURN    
  18. END  
Above function return information of all project that that’s Project_Id is greater than given Project_id.  
  1. SELECT EMP.Project_Id,COUNT(EMP.Project_Id) AS [Total] FROM dbo.Employee AS EMP    
  2. OUTER APPLY    
  3. [fun_Return_Info](EMP.Project_Id)    
  4. GROUP BY EMP.Project_Id    
Output
 
id  
 

APPLY with TOP Command

  1. SELECT * FROM    
  2. (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    
  3. LEFT OUTER JOIN    
  4. dbo.Employee AS EMP    
  5. ON    
  6. PRO.Project_Id=EMP.Project_Id)Tab1    
  7. WHERE Tab1.Rank<=2   
Output
 
runAPPLY with TOP Command 
 
Above query return top Employee details for each project. We can perform same operation using APPLY.
  1. SELECT PRO.*,Tab.* FROM dbo.Project AS PRO    
  2. 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    
Above query produce same result as previous query.
 
Example 3
  1. DECLARE @Tab TABLE    
  2. (    
  3.    [State] [nvarchar](max),    
  4.    City [nvarchar](max)    
  5. )    
  6.     
  7. INSERT INTO @Tab    
  8. SELECT 'Rajasthan','Alwar,Laipur,Ajmer,Kota' UNION ALL    
  9. SELECT 'Haryana','Hisar,Jhajjar,Rohtak' UNION ALL    
  10. SELECT 'Maharaster','Mumbai,Pune'    
  11.     
  12. SELECT * FROM @Tab AS [@TA]    
Output
 
APPLY with TOP Command 
 
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.
  1. ALTER FUNCTION SplitString(@Input NVARCHAR(MAX))  
  2. RETURNS @Output TABLE(City NVARCHAR(1000))  
  3. AS  
  4. BEGIN  
  5. DECLARE @Index int;  
  6. SET @Input = @Input + ',';  
  7. WHILE(LEN(@Input) > 0)  
  8. BEGIN  
  9. SET @Index = CHARINDEX(',', @Input);  
  10. INSERT INTO @Output(City)  
  11. VALUES(SUBSTRING(@Input, 0, @Index))  
  12. SET @Input = SUBSTRING(@Input, @Index + 1, LEN(@Input));  
  13. END  
  14. RETURN  
  15. END  
Now we use this function to split the city name.
  1. DECLARE @Tab TABLE    
  2. (    
  3. [State] [nvarchar](max),    
  4. City [nvarchar](max)    
  5. )    
  6.     
  7. INSERT INTO @Tab    
  8. SELECT 'Rajasthan','Alwar,Laipur,Ajmer,Kota' UNION ALL    
  9. SELECT 'Haryana','Hisar,Jhajjar,Rohtak' UNION ALL    
  10. SELECT 'Maharaster','Mumbai,Pune'    
  11.     
  12. SELECT T.State,Tab.*FROM @Tab AS T    
  13. OUTERAPPLY dbo.SplitString(T.City)Tab  
Output
 
APPLY with TOP Command 
 
Thanks for reading the article.