Cross apply and outer apply in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 91 of signal server
tutorial in this video well discuss the
cross supply and outer apply operators
in signal server with examples for the
purpose of this demo we'll be using
these two tables Department and employee
here is the sequal script to create
those tables and populate them with test
data
I'll have the script available on my
blog in case you need it Department ID
column in this employee table is a
foreign key referencing the ID column
from Dept table now what we want to do
is write a query that's going to
retrieve all the matching rows between
these two tables within our results hat
we want
Department name from Dept table and the
employee name gender and salary from
employee table so the result set should
look like this notice we want only the
matching rows between these two tables
now we can very easily achieve this
using an inner join so let's quickly
write that in a joint query so we want
to select star from Dept table let's
give it an alias D and we want to inner
join the department table with employee
table let's give an alias for this
employee table E and we need to specify
the join condition d dot ID equals e dot
department ID and what are the columns
that we want in our result set we want
department name from the department
table employee named employee gender and
employee salary so if we execute this
query we should get all the matching
rules between department and employee
table notice within the results that we
don't have any rows for administration
and sales departments that's basically
because within the employee table we
don't have any employees belonging to
that departments right so that's the
reason why we don't have those roles in
the result set now let's say we want to
write another query which is going to
retrieve all the matching rows plus non
matching rows from the department table
we can very easily achieve that using a
left join instead of an inner join so
when we perform a left join between
department and employee table we are
going to get all the
from the department table irrespective
of whether they have a matching record
in the employee table or not notice we
have administration and sales
departments here since they don't have
any employees in the employee table the
name gender and salary column from the
employee table have nulls
all right now let's say for some reason
we don't have access to this physical
table employee instead they have given
us a function and that's a table valued
function so if you give the department
ID to that function it's going to give
you the list of all employees belonging
to that department first let's go ahead
and create that function so we want to
create a function let's call it FN
underscore get employees by department
ID so we want to pass department ID to
this function let's call that department
ID and the data type is integer so this
is going to be a table valued function
so this returns a table as returned and
then we'll specify our query here so
select star from employee table where
department ID equals our input parameter
at department ID so let's go ahead and
create this function so we have created
a function now let's quickly test this
select star from the name of our
function is FN underscore get employees
by department ID so for example if we
pass department ID 1 this function is
going to return us all the employees
within that department
so notice department ID is 1 all right
now what we want to do is we want to
join the department table with this
table valued function now to do that can
we use an inner join or the left join if
we try to do that you know we are going
to get an error let's look at that
quickly in action so here on the Left we
have our department table now
what I want to do is join that where
this function the table valued function
and if you look at this function it has
got an input parameter Department ID and
I'm going to use this table name and
pass d dot ID there and the Aaliyah's is
still going to be e okay now look at
this when I execute this query we are
going to get an error look at that it
says the multi-part identifier d dot ID
cannot be bound okay so you cannot do an
inner join like this between a physical
table and the table valued function so
this is where we use the cross apply and
outer apply operators in sequel server
now I'm simply going to replace this
inner join with cross apply operator
okay so the cross apply operator is
going to semantically behave like an
inner join whereas an outer apply
operator is semantically going to behave
like and left outer join so when we use
cross apply we don't require any join
condition because all this function
requires is a department ID and on the
left here we have a table Department and
we are passing the department ID so this
function right here gets called for
every row within the department table
okay and the department ID is passed the
employees are retrieved and you know the
department name column is retrieved for
every row from the department table and
employee name gender and celery are
retrieved from this function okay so
what don't require this joint collation
so let's go ahead and execute this now
we should get the same result only the
matching rows between those two okay
look at this when I execute this I get
in our only ID payroll and HR
departments where there are employees
whereas administration and sales we
don't have that in the result set
so if you want even the non matching
rows from the department table then
instead of cross apply use outer apply
so let's make a copy of this
now to apply now when we execute this we
should get the non matching rows from
department table as well and notice the
columns from the table valued function
are now so this is semantically
equivalent to left outer join so what
does this cross apply and outer apply
operators in sequence server
these operators are actually introduced
in sequence over 2005 and they are used
to join a table with 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 table this left table is also
usually called as out table cross apply
returns only matching row so it's
semantically equivalent to an inner join
whereas outer apply returns matching
plus non matching rows so this is
semantically equivalent to a left outer
join the unmatched columns of the table
valued function will be set to nulls as
we can see here in the result set so
named gender and salary are coming from
the table valued function and since we
don't have you know any result produced
for these two rows you know from the
Dept table those columns from the table
valued function will be set to null so
here we have the cross apply operator
example and here we have the outer apply
operator example thank you for listening
and have a great day