Advanced or intelligent joins in sql server - Part 13 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to prism technologies I am
venket this is part 13 of sequel server
in this session we'll discuss about
advanced or intelligent joints in sequel
server as part of this we will see how
to retrieve only the non matching rows
from the left table and along the same
lines how to retrieve only the non
matching rows from the right table and
finally we will see retrieving only the
non matching rows from both the left and
right tables before continuing with the
session I strongly recommend watching
part 12 of this video series now in part
12 of this video series we have seen how
inner join left join right join and full
join works we have seen examples of that
if you recollect from that part inner
join retrieves only the matching rows
between both the tables whereas left
join retrieves the matching rows plus
non matching rows from the left table
and right join matching rows plus non
matching rows from the right table full
outer join matching rows from both the
tables plus non matching rows from the
left table as well as non matching rows
from the right table now if you look at
all these joints you know the queries
underneath them they are pretty much
identical and the only difference you
find is the type of join rest of the
query you know is almost identical so if
you look at here its inner join but
whereas in the left join you just say
its left joint for right join right join
and full join you say full join that's
the only difference otherwise the query
is identical all right now let's assume
okay so if you look at these four types
of joins here all the types of joins you
know they give you the matching rows
plus non matching parts okay so but
let's say I want an output like this
okay I want only the non matching rows
from the left table and along the same
lines I want only the non matching rows
from the right table and maybe finally I
want only the non matching rows from the
left
and the non-matching rose from the right
table okay so basically in this three
result sets we are we are actually
excluding the matching path in the
previous slide we see in all the four
types of joins we are getting the
matching rows plus something extra okay
but here no matching rows at all non
matching rows from the only non matching
words from the left table only non
matching rows from the right table non
matching rows from the left table and
non matching rows from the right table
and how do we do that
you know it's a it's a it's very easy to
understand this you know you write your
query if you want only the non matching
rows from the left table you write the
query as you do it for left join and
then you will have a where clause okay
let's see how to do that now if you look
at these two tables here it's the same
employees and departments table we have
been working with in part well of this
video series so if you look at this
employees table you know James and
Russell records have got department IDs
now okay so this is a left table and
this is alright table now I want only
James and Russell's records which means
the non matching rows in the left table
okay so how do we do that let's write a
query for that so when you write your
query the output should be like this
name gender salary department name for
James and Russell
there is no matching row in the
department's table that's why department
name will be now okay but basically I
want only those non matching rows from
the left table so let's see how to write
the query for that so select these
columns whatever you want from was the
name of the table TBL employee and we
want to left join with that table and
which table is that TBL department table
and obviously join condition on TBL
employee dot department ID is equal to
departments table
TBL department dot ID so that's our
joint condition so if you look at this
result I mean this query now it's a left
to join query now if we execute this
query we get all the ten rows from the
left table okay
the matching rules plus the non matching
rows so that's what is the inner join
query I mean left join query select
whatever columns we want from employee
left join department on you know the
join condition so we get the matching
rows as well as the non matching rows
but what's our a target our target is
basically to retrieve only the non
matching rows from the left table which
means we want only James and Russell's
records which means we have to filter
out all these these eight rows okay so
obviously if we have to filter we will
use the where Clause okay and there are
two ways to achieve this one is look at
this you can simply say okay I want all
these rows but I want to filter out okay
where employee table you know I
basically want only the rows which has
got department ID as null so if you look
at this who has got department ID as
null James and Russell in the employees
table so what you can basically do here
is you can say TBL employee dot
department ID is now so the moment you
do that what's going to happen then we
press f5 we get all these rows you know
all this only the non matching rows from
the left table another way to do it is
okay if you look at the presentation you
know another way is to do it as
department dot ID is null so department
table dot
Heidi is now so when we execute this
query we get the same result and and
it's pretty simple to understand what
you are saying okay when you when you
say okay I want to do a left to join
what do we get we get everything the
matching rows plus the non matching rows
from the left table okay so but we want
to filter out James and Russell's record
so as part of that what is the condition
we are using we are saying Department ID
is now so basically for all these
records department ID is not null hence
they will be filtered out only these two
records has got Department IDs now so
you know we don't have a match you know
as it is we get all the records from the
left table so these records who has a
department ID will be filtered out so
that's how we get only the non matching
rows from the left table now let's say I
want you know if you look at this one I
want only the non matching rows from the
right table now if you remember how do
we write it right join instead of left
join you just say right join and when we
do this what do we get we get you know
right join will basically give us all
the matching rows between the two tables
plus the non matching rows from the
right table so there are eight matching
rows between TBL employee and department
so we get those eight rows plus one
additional record you know this other
department record does not have you know
know know employee is assigned that
department okay so so this is the non
matching row in the right table okay so
we get this row as well as the matching
rows between these two tables you know
these eight if we are using a right
joint but what is our intention our
intention is basically to retrieve only
the non matching rows from the right
table which means I just want this other
department row okay so how do we do that
all you need to do is look at this we
basically want to check okay wherever
the employee dot department ID is now
okay so basically if you look at this
right join here what is it doing it's
giving you all the matching rules plus
the non matching rows from the right
table okay now when you when you say
we're either employees or Department ID
is null for all of these Department IDs
is not null so obviously these rules
will be filtered out which means these
matching rules will also be filtered out
and you only be left with this other
department row so when I say TBL
employee that department ID is now you
only get that other department so we
only get the non matching words from the
right table okay now let's say I want
the non matching rows from the left
table plus the non matching rows from
the right table by now you would have
already guessed what to do okay you will
just use an or or condition okay so in
the where Clause will say TBL dot
employee TBL that employee the
department ID is now or TBL dot
Department dot
Heidi's now so what's going to happen we
are going to get only the non matching
rows we are saying okay I mean look at
this when I run this query I am NOT
getting the non matching rows from the
left table why because you are using a
right join so you will have to use a
full join there so what does full join
give you full join will give us all the
matching rows between both the tables
plus the non matching rows from the left
table plus the non matching rows from
the right table so if we execute the
query until here we end up getting 11
rows okay eight matching rows two non
matching rows from the
stable and one non-matching row from the
right paper but our our intention is to
filter out these matching rows these
eight matching rows you we only want the
non matching rows from the left table
and the non matching row from the right
table so in order to do that we are
using this where clause so when we
execute this now we should only get the
non matching rows from the left table as
well as from the right table so we get
these three rows all right and one
important thing to keep in mind is never
ever use you know equal to when
comparing null value you cannot say you
know TBL employee dot Department ID is
equal to now and along the same lines
here you cannot say you cannot use equal
to operator to compare null since equal
server
you always have to use is keyword
alright 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