Self join in sql server - Part 14 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 14 of signal
server in this session we'll talk about
self join which is nothing but joining a
table with itself we'll also talk about
self join classification most of the
people think self join is a different
type of join altogether which is not
true self join can be classified as in a
outer across join depending on how you
write the self join before continuing
with the session I strongly recommend
watching parts 12 and 13 of this video
series now in parts 12 and 13 we have
already seen how to join two different
tables specifically we have been working
with TBL employees and TBL departments
tables but have you ever thought of a
need to join a table with itself if you
look at the table here that we have it
has got three columns employee ID name
and manager ID now let's say I want you
to write a query which gives me an
output like this I want the employee
name as well as his manager's name okay
now if you look at the table on the
left-hand side if you look at Mike
Mike's manager ID is three and you take
this three and look up in the employee
ID column okay three is stored so Mike's
manager is stored and along the same
lines if you look at Rob is manager IDs
one and you take this one and look up an
employee ID column one is Mike okay
so Rob's manager is Mike and we get that
output here so if you look at the
situation here we are using this manager
ID column and looking up in the employee
ID column within the same table which
means we are you know referencing the
same table okay so here if we want this
kind of an output like employee name and
managers name there is a need to join
this employee table with itself in patch
12 and 13
we have joined TBL employees and TBL
department tables but then in TBL
employee table I had department ID
column which I have used to look up the
ID column in TBL departments table which
is another table but here I am using
this manager ID column to look up
employee ID column in the same table so
there is a need to reference the same
table to join with the Tate same table
and that's nothing but a self join now
you might be wondering how can we join
the same table with itself
okay so if you look at this here this
query right here you know we have done
exactly the same thing okay select first
of all whatever the we'll talk about
these columns just in a bit okay so if
you look at the output we want the
employee name and the manager name so
select employee name manager name will
come to those bits in a in you know the
column list in the Select clause in a
bed if you look at the front Clause we
are saying TBL employee and we are
giving it an at Table Ali as e and we're
saying okay join this table left to join
we are doing a left join with TBL
employee again so if you look at this
you know forget about the type of join
here it can be inner join left joint
whatever okay so select whatever columns
you want from TBL employee give it an
alias e join with the same table TBL
employee and give it another ally as me4
employee m4 manager so you can assume
now we have two instances of this table
you know two copies of the stable rather
employee table and then another table
like manager table okay
but both of the structures will be
identical you know you're basically
referencing the same table and then what
you are doing is in employee table take
the manager ID column okay in the
employee table take the manager ID
column and look up the employee ID in
the manager table which is nothing but
another copy of the same table okay and
then from the employees table which is
nothing but e I want the name which is
nothing but the employee name
so I'm giving it an Emily s as employee
and from the managers table I am getting
the name which is nothing but the
manager name okay so so that's how we
get this output if you look at the join
here we are joining with the same table
so this is nothing but a self join so
let's write this in signal server
management studio so we'll come to the
column list in a bed okay select from
from which table from TBL employee let's
give it an ally as e left join with the
same table TBL employee but then I'm
going to call this M for manager so this
is an employee table this is the manager
table and how do we want to join that
okay in the employees table I want to
take the manager ID column and look that
up in the manager table in the employee
ID column so lookup employee ID column
in manager table using the manager ID
column in employee table you are
treating the same table as two different
tables now which means you are actually
joining the same table with itself so
what columns do you want now from the
employee table if I pick up the name it
will be an employee name so give it a
meaningful name
so as employee give it an aleeah's and
similarly from manager table I want the
name but this is going to be the manager
name so manager so that's it so when we
execute this query we get the output as
we expect okay now look at this we are
using a left to join here and if you
remember from part well and 13 of this
video series left join will give you all
the matching rows between these two
tables plus non matching rows from the
left table okay but if it's an inner
join it will only give the matching rows
between the two tables which means look
at this when we were using
to join we were getting thoughts record
as well but the moment I change it to
inner join okay see if you look at the
output right now we've got five rows
including the thoughts record now when I
execute this one look at what's going to
happen I only get four rows what happens
to Todd record it's not retrieved
anymore why because if you look at
Todd's a record the manager ID does not
match with any of the employee IDs there
is no matching record and if you
remember in a joint we'll only give you
the matching records between the two
tables now you might be wondering where
are two tables here but remember in the
joint we are joining this table with
itself so we are treating a single table
like two tables we're joining the same
table with itself okay so when you are
using this column to look up in the
employee ID column of the same table
if there are no matching rows and you're
doing and in a I mean the matching rows
are not returned if you do an inner self
join so you can call this as an inner
self join okay
why is it called a self join because you
are joining the same table with itself
and you are doing an inner join so inner
self join and along the same lines you
can call this query you know you're
joining if you look at the first picture
here we are joining TBL employee with
manager but we are doing a left joint so
you can call this as left outer self
join okay so along the same lines you
can do a right outer self join and a
full outer self join so a self join is
not a different type of join all
together it can be classified under any
type of join inner outer any of the
outer joins left right or for and cross
join as well we have seen how to write a
self you know left outer self join inner
self join but we haven't seen how to
write a cross self join it's pretty much
identical if you remember from part 12
of this video series a cross join cannot
have an on clause so across all self
join can also can I mean each hole it
also shouldn't have the on Clause so if
you want to convert this into a cross
join the first thing you have to do get
rid of the on Clause and then convert
this inner join into cross join that's
about it so
we are joining the same table with
itself we call it you know a self join
but since we are using cross join it's
called as cross self join okay now
remember in the employee table we've got
five rows and if you remember from the
definition of the cross join when you do
a cross joint between two tables you get
the Cartesian product which is nothing
but the number of rows in the first
table multiplied by the number of rows
in the second table okay now here it's
the same table so in the the same table
you know we're joining employees table
with itself and employee table we have
got five rows and the manager table
obviously we have another five rows so
when we execute this query we get five
multiplied by five which is twenty five
rows so when I press f5 look at that in
the output you should see there are 25
rows so keep in mind you know it's very
simple to understand joining a table
with itself it's nothing but a self join
and self join it's not a different type
of join altogether self join can be
classified under any type of joints like
inner outer cross join depending on how
you write yourself joint query now if
you look at the output here when we do a
left when we do a left join
on employee dot manager ID is equal to
manager dot employee ID look at this
when we execute this query we get
towards record you know Todd doesn't
have a manager so the manager name is
now now instead of showing null you know
I want to display since Todd doesn't
have a manager maybe I want to display a
text here saying no manager or I want to
say super boss or super manager whatever
instead of null I want to show my own
string here how do I do that there are
several ways you can do it okay which
we'll be talking about in our next
session on this slide you can find
resources for asp.net and C happen to be
questions that's it for today thank you
for listening
have a great day