3/12/2023 Sql server, .

net and c# video tutorial: Cross apply and outer apply in sql server
The Wayback Machine - https://web.archive.org/web/20210918045027/https://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-o…

Sql server, .net and c# video tutorial

Free C#, .Net and Sql server video tutorial for beginners and intermediate programmers.

Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Cross apply and outer apply in sql server

Suggested Videos
Part 88 - Difference between except and not in sql server
Part 89 - Intersect operator in sql server
Part 90 - Difference between union intersect and except in sql server

Pragim Technologies - Best software

training and placements in marathahalli,
bangalore. For further details please call
09945699393.

Complete Tutorials
How to become a full stack web
developer

Cloud computing complete tutorial

In this video we will discuss cross apply and outer apply in sql server with examples. Healthy food for healthy mind and
body

JavaScript tutorial

Bootstrap tutorial

Angular tutorial for beginners

Angular 5 Tutorial for beginners

https://web.archive.org/web/20210918045027/https://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql.html 1/5
3/12/2023 Sql server, .net and c# video tutorial: Cross apply and outer apply in sql server

Important Videos
The Gift of Education

Web application for your business

How to become .NET developer

Resources available to help you

Dot Net Video Tutorials

Blazor tutorial

C tutorial

ASP.NET Core Tutorial

We will use the following 2 tables for examples in this demo
ASP.NET Core Razor Pages Tutorial

Angular 6 Tutorial

Angular CRUD Tutorial

Angular CLI Tutorial

Angular 2 Tutorial

Design Patterns

SQL Script to create the tables and populate with test data SOLID Principles
Create table Department
( ASP.NET Web API
Id int primary key,
DepartmentName nvarchar(50) Bootstrap
)
AngularJS Tutorial
Go
jQuery Tutorial
Insert into Department values (1, 'IT')
Insert into Department values (2, 'HR') JavaScript with ASP.NET Tutorial
Insert into Department values (3, 'Payroll')
Insert into Department values (4, 'Administration') JavaScript Tutorial
Insert into Department values (5, 'Sales')
Go Charts Tutorial

LINQ
Create table Employee
( LINQ to SQL
Id int primary key,
Name nvarchar(50), LINQ to XML
Gender nvarchar(10),
Salary int, Entity Framework
DepartmentId int foreign key references Department(Id)
) WCF
Go
ASP.NET Web Services

Insert into Employee values (1, 'Mark', 'Male', 50000, 1) Dot Net Basics
Insert into Employee values (2, 'Mary', 'Female', 60000, 3)
Insert into Employee values (3, 'Steve', 'Male', 45000, 2) C#
Insert into Employee values (4, 'John', 'Male', 56000, 1)
Insert into Employee values (5, 'Sara', 'Female', 39000, 2) SQL Server
Go
ADO.NET
We want to retrieve all the matching rows between Department and Employee tables.
ASP.NET

GridView

ASP.NET MVC

Visual Studio Tips and Tricks

Dot Net Interview Questions

Slides
https://web.archive.org/web/20210918045027/https://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql.html 2/5
3/12/2023 Sql server, .net and c# video tutorial: Cross apply and outer apply in sql server

Entity Framework
This can be very easily achieved using an Inner Join as shown below.
Select D.DepartmentName, E.Name, E.Gender, E.Salary WCF
from Department D
Inner Join Employee E ASP.NET Web Services
On D.Id = E.DepartmentId
Dot Net Basics
Now if we want to retrieve all the matching rows between Department and Employee
C#
tables + the non-matching rows from the LEFT table (Department)
SQL Server

ADO.NET

ASP.NET

GridView

ASP.NET MVC

Visual Studio Tips and Tricks

This can be very easily achieved using a Left Join as shown below. Java Video Tutorials
Select D.DepartmentName, E.Name, E.Gender, E.Salary Part 1 : Video | Text | Slides
from Department D
Left Join Employee E Part 2 : Video | Text | Slides
On D.Id = E.DepartmentId
Part 3 : Video | Text | Slides
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 Interview Questions
department by Department Id. C#

Create function fn_GetEmployeesByDepartmentId(@DepartmentId int) SQL Server

Returns Table
as Written Test
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

https://web.archive.org/web/20210918045027/https://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql.html 3/5
3/12/2023 Sql server, .net and c# video tutorial: Cross apply and outer apply in sql server
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.

7 comments:

Anonymous September 8, 2015 at 1:37 PM

Please discuss Sequence in SqlServer
Reply

Nawaraj Subedi September 9, 2015 at 12:39 AM

Thank you very much for great video tutorial.This is very helpful to me and others also
.My request is also upload the videos of OVER ( ) CLAUSE and Partition also Thank
You...!!!!
Reply

Bhargava November 24, 2015 at 3:48 PM

Hi Venkat,
In the above Outer Apply you have mentioned it is same as left outer join. Any specific
reason why you have mentioned left "OUTER" join. Left join and left outer join are the
same. Please correct me.
Reply

Replies

Unknown December 17, 2015 at 8:58 AM

OUTER JOIN IS 3 THREE TYPES.
LEFT OUTER JOIN
RIGHT OUTER JOIN

FULL OUTER JOIN.

THERE IS NO DIFFERENCE BETWEEN LEFT JOIN AND LEFT OUTER

JOIN.
I HOPE IT CLEARS YOUR DOUBT.

https://web.archive.org/web/20210918045027/https://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql.html 4/5
3/12/2023 Sql server, .net and c# video tutorial: Cross apply and outer apply in sql server

Reply

Unknown December 14, 2018 at 2:04 AM

You say outer apply is semantically equivalent to left outer join can you draw the same
parallel with right outer join?
Reply

Aqib April 9, 2019 at 2:13 PM

SELECT D.DepartmentName, E.Name,E.Gender,E.Salary
FROM Employee E
RIGHT JOIN Department D
ON E.DepartmentId = D.Id

HOW to Apply OUTER APPLY JOIN on this

Reply

Replies

blogspot June 30, 2020 at 11:08 AM

SELECT D.DepartmentName, E.Name,E.Gender,E.Salary
FROM Department D
OUTER APPLY fn_GetEmployeesByDepartmentId(D.Id) E

Reply

It would be great if you can help share these free resources

Newer Post Home Older Post

Subscribe to: Post Comments (Atom)

Powered by Blogger.

https://web.archive.org/web/20210918045027/https://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql.html 5/5