Cross apply and outer apply in sql server

cross apply sql server example
sql server outer apply example
sql server join table function with parameter
sql server inner join table valued function

In this video we will discuss cross apply and outer apply in sql server with examples.

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

We want to retrieve all the matching rows between Department and Employee tables.

This can be very easily achieved using an Inner Join as shown below.
Select D.DepartmentName, E.Name, E.Gender, E.Salary
from Department D
Inner Join Employee E
On D.Id = E.DepartmentId

Now if we want to retrieve all the matching rows between Department and Employee tables + the non-matching rows from the LEFT table (Department)

This can be very easily achieved using a Left Join as shown below.
Select D.DepartmentName, E.Name, E.Gender, E.Salary
from Department D
Left Join Employee E
On D.Id = E.DepartmentId

Now let's assume we do not have access to the Employee table. Instead we have access to the following Table Valued function, that returns all employees belonging to a department by Department Id.
Create function fn_GetEmployeesByDepartmentId(@DepartmentId int)
Returns Table
as
Return
(
Select Id, Name, Gender, Salary, DepartmentId
from Employee where DepartmentId = @DepartmentId
)
Go

The following query returns the employees of the department with Id =1.
Select * from fn_GetEmployeesByDepartmentId(1)

Now if you try to perform an Inner or Left join between Department table and fn_GetEmployeesByDepartmentId() function you will get an error.
Select D.DepartmentName, E.Name, E.Gender, E.Salary
from Department D
Inner Join fn_GetEmployeesByDepartmentId(D.Id) E
On D.Id = E.DepartmentId

If you execute the above query you will get the following error
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "D.Id" could not be bound.

This is where we use Cross Apply and Outer Apply operators. Cross Apply is semantically equivalent to Inner Join and Outer Apply is semantically equivalent to Left Outer Join.

Just like Inner Join, Cross Apply retrieves only the matching rows from the Department table and fn_GetEmployeesByDepartmentId() table valued function.
Select D.DepartmentName, E.Name, E.Gender, E.Salary
from Department D
Cross Apply fn_GetEmployeesByDepartmentId(D.Id) E

Just like Left Outer Join, Outer Apply retrieves all matching rows from the Department table and fn_GetEmployeesByDepartmentId() table valued function + non-matching rows from the left table (Department)
Select D.DepartmentName, E.Name, E.Gender, E.Salary
from Department D
Outer Apply fn_GetEmployeesByDepartmentId(D.Id) E

How does Cross Apply and Outer Apply work
The APPLY operator introduced in SQL Server 2005, is used to join a table to a table-valued function.
The Table Valued Function on the right hand side of the APPLY operator gets called for each row from the left (also called outer table) table.
Cross Apply returns only matching rows (semantically equivalent to Inner Join)
Outer Apply returns matching + non-matching rows (semantically equivalent to Left Outer Join). The unmatched columns of the table valued function will be set to NULL.

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql_8.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists