Joins in sql server - Part 12 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

Oh welcome to presume technologies I am
linkit this is part 12 of signal server
in this session we'll understand the
different types of joints that are
available in sequel server specifically
cross join in a joint and outer joints
before continuing with this session I
strongly recommend watching parts three
and five of this video series now joins
in sequel server I used to retrieve data
from two or more related tables in
general tables are related to each other
using foreign key constraints so I have
this employee table on the right here
which is got ID name gender salary and
department ID columns and then I have
the department table table itself here
which has got the department name the
location that Department is present at
and the department head now if you look
at these these two tables are related
using this foreign key Department ID now
if you are not sure what foreign key is
please watch part 3 of this video series
so these two are related tables now
let's say I asked you to write a query
which will give me an output as shown
here on the left hand side I want the
name of the employee gender salary and
the department name and if you look at
the output columns that I require the
first three columns name gender and
salary they are present in the employee
table whereas the department name is
present in the department's table so
both you know these columns all these
columns are not coming from a single
table their spread across two different
tables so obviously if I have to
retrieve this output then I will have to
join these two tables okay so to join
these two tables in sequel server we
have got different types of joints for
example we have inner join outer join
and cross joins and again the outer
joins in sequel server which we'll be
talking about in a bit are divided into
three different categories left outer
join right outer join or full and full
outer join okay you can say left outer
right outer full load or you can just
say left join right join or full join
now let's talk about inner join now
let's say you know I want output like
this you
I want the name of the person their
gender salary and the department name
okay so let's see how we do this using
inner join okay and look at this if you
have two tables you know in the inner
join the matching rules between the two
tables are retrieved for example if you
look at the employee table here you know
everybody has department ID except
james-andrew so if you look at James and
Russell's record the department ID are
now which means these rows
doesn't have a matching department ID
from the department you know departments
table so when I join these two tables
using inner join only the matching rows
between these two tables are retrieved
which means the matching rows between
these two tables are you know these
eight rows from 1 till 8 which is what I
am getting and if you look at the output
I don't get James and Russell's records
why because their department ID does not
match with the ID column in the
department's table so inner join will
only give you matching rows between both
the tables involved in a joint now let's
see how to write the inner join query
itself so if you look at the two tables
that we have in employee table we have
got ten rows and in the departments
table we have got four rows and what do
we want we want the name gender salary
and department name columns in the
output from which table from TBL
employee table and you know the first
three columns are present in TBL
employee table but the department name
column is present in the department's
table so I will have to join with that
table and to join with that table you
use the join keyword so PBO department
so we are saying I want to join TBL
employee table with TBL department table
okay now if you want to join these two
tables how do you want to join these two
tables what's the common column between
them the common column is the department
ID column and to specify the join
condition
you use the on claws so on TBL employee
so in the table employee we have the
department ID column so use that to look
up TBL department table and the ID
column within that table okay so it's
pretty simple select whatever columns
you want from you know TBL employee now
I want to join this table with the TBL
department so we use the join keyword on
how do you want to join those two tables
now between these two tables the common
column is the department ID you know
department ID in TBL employee table join
that with ID column in TBL department
table so obviously if we execute this
query we should get eight records okay
now if we execute all of them together
okay and if you look at the output here
in the messages in the TBL employees
table we've got eight rows and
departments we've got four rows and in
the join query we only got eight rows
why because if you look at James and
Russell their records doesn't have a
matching department ID
that's why inner join has not returned
those two records so if you look at the
join output we don't have James and
Russell's records in the output we only
have the matching records between these
two tables
okay and when you write an inner join
query you can say inner join or just
join I would say always write inner join
because this will say your intentions
explicitly okay alright so that's about
inner join so inner join returns only
the matching rows between both the
tables non matching rows are eliminated
okay
now let's say my requirement is in such
a way that I not only want the matching
rows I want the non matching rows as
well from the employees well basically
I'm saying I want all the employees
irrespective of whether they are
assigned to a department
and I want all the employees so how do
we do that so indirectly you are saying
okay I want the matching rows plus the
non matching rows from the employees
table which means you want everything
from the left table matching and non
matching okay that's when you actually
go for left to join so if you look at
left to join returns all the matching
rules plus non matching rows from the
left table so there are eight matching
rows so this department ID matches all
for these eight rules the department IDs
match with the department IDs but these
two rows doesn't have a matching
department ID okay so but in departments
table we have total ten rows so all
matching and non matching rows will be
retrieved when you use a left join so
when we write a left join query we
should get ten rows so how do we write
the left join query you know everything
remains exactly similar except that
instead of inner join you will say left
join that's it look at this previously
we have got eight records when we
executed that query but let's now
execute this and see how many records we
get so we first go to the messages look
at this in employees table we have got
ten rows in departments four rows and in
the query left to join query that we
have written we have got ten rules again
so if you look at the output now we
should have caught both James and
Russell's records as well but if you
look at the department name column it's
basically not and it makes sense why
because they don't have a department ID
they are not assigned a department yet
so that's why the department name will
be now okay so left to join basically
returns all the matching rules between
both the tables plus non matching rows
from the left table and this picture
depicts exactly that scenario and when
you use a left join you can say left
just left join or you can say left outer
join okay the query results will not be
affected so the outer keyword is
optional there all right
now let's say I want all the rows from
the right table including if you look at
the two tables here you know you have a
department called other department and
that departments ID is for now this new
employee in the employees table belongs
to this other department okay so if
there is a requirement you know if
somebody asks you okay give me all the
employees okay which means I want all
the matching records between both the
tables plus you know non matching
records from the right table okay so
from the department's table I want you
know other department record as well
irrespective of whether there is an
employee assigned to that department or
not so we want all the rows from the
right table okay plus I mean all the
rows including the non matching ones
okay so basically right join will return
you all the matching roles between both
the tables plus non matching rows from
the right table okay so let's see what
happens you know if we convert this left
outer join to right outer join okay so
between employees and department table
there are eight matching rows and in the
TBL department table there is this other
department which you know no other
employ I mean no employee belongs to
this other department okay so obviously
this other department true does not have
a matching record in employees table
okay and we want this record as well
okay so how do we do that using right
outer join so when we execute this we
should get around nine records so nine
records so look at this all the matching
rows until Sarah we've got and other
department row okay and if you look at
the name gender and salary columns are
null why because no employee is assigned
to this department so obviously will not
have any name gender or salary okay so
obviously whenever you want to return
all the matching roles between both the
tables that are involved in a joint plus
non matching rows
from the right table that's when we go
for right outer join okay so you might
have guessed by now if I want all the
matching rows between both the tables
plus non matching rows from the left
table and non matching rows from the
right table then what type of join do I
use full join so full outer join gives
me exactly that result these are the
matching rows from both the tables these
two are non matching rows from the left
table and the last row here other
department is the non matching row in
the right table full outer join will
give you all the records from both the
left and right tables including the non
matching rows okay so obviously when we
execute this query we should get around
eleven records eight matching records
plus two non matching from left and one
non matching from the right table so how
do we write a full outer join query just
specify full outer join so when we
execute this query you should see until
here all these matching records these
two are non matching records in the
employee table and this is a non
matching record in the department's
table so together we have eleven records
again you can specify full outer join
or just say full join okay so so far we
have seen inner join left join right
join and outer join full outer join okay
but apart from all these types of joints
we also have another joint called cross
join so what does it cross join do and
how do I write that query first let's
write the query you know and we look at
that but and then we'll decide what
cross join is going to do so if it's a
cross join instead of saying full outer
join
all I say is cross join now remember a
cross join will not have a non class
okay I'll tell you in a bit why a cross
join shouldn't have a non Clause so when
I delete that cross join and then when I
execute this query look at this look at
the number of rows that we are getting
40 rows and if you remember how many
rows are there in these tables ten rows
in the employees table and
for roles in the department's table and
when I when we execute the cross join
query we are getting 40 rows so
basically what's happening the number of
rows in the employees table is being
multiplied by the number of rows in the
department's table so a cross join will
give us the Cartesian product of the
tables that are involved in a cross join
okay so that's what cross join is all
about cross join produces the Cartesian
product of the two tables involved in
the joint for example in the employees
table we have ten records in the
department's table if we have four
records a cross join would produce 40
rows between these two tables and as I
told you cross join shouldn't have an on
clause okay so what basically a cross
join does is it will take each record
from the right table and then associate
that with every record in the left table
so in the department's table we have got
four rows you know ID payroll HR and
other department so it will take ite
Juro from the department's table
associate that to every one of the
employees in the employees table and
similarly it will take the next record
which is payroll and associate that so
it basically gives us you know the
number of rows in the employees table
multiplied by the number of flows in the
department's table so it gives us a
Cartesian product and since this gives
us a Cartesian product it doesn't really
make sense to have you know an on cloth
cross join okay and look at this so far
we have seen you know writing query for
joins you know we have seen how to write
inner join left outer right out of
loader and cross join now you know
depending on what we have done until now
it's pretty simple to to develop a
generic formula for joints so what is
that select what columns you want or
just say column lists okay select your
column list for example I want name
gender salary department name etc select
column list from you know maybe your
left
the table and then you specify the join
type in a joint cross join whatever and
once you specify the join type you know
with which table you are joining so
right table and on clause for specifying
join condition okay
so if you compare this with what we have
okay look at this if it's an inner join
you know it would have been something
like this so inner join and on we will
have TBL department dot ID is equal to
TBL employee dot department ID so if you
compare this generic formula with this
inner join query you know it exactly
fits and select whatever columns we want
from Clause the left table and then the
join type here it's an inner join so we
are specifying inner join otherwise if
it's across you know left join with a
left join right right join etc and then
finally on clause and your join
condition so this is the genetic formula
you know if you have hard time you know
remembering the syntax for joins alright
so in summary cross join returns the
Cartesian products of the tables
involved in the join
whereas inner join returns only the
matching rules non matching rows between
the tables are eliminated you know look
at these pictures which will make the
understanding of joins pretty much easy
okay so if you look at the energy only
matching rows left join matching rows
plus non matching rows from the left
table and right join matching rows plus
non matching rows from the right table
whereas full join matching rows between
both the tables non matching from the
left and non matching from the right
okay
on this slide you can find resources for
asp.net and C sharp interview questions
that's it for today thank you for
listening have a great day