This content explains how SQL JOIN operations (INNER, LEFT, RIGHT, FULL OUTER) behave when dealing with duplicate keys and NULL values in two tables, clarifying the resulting number of records for each join type.
Mind Map
Click to expand
Click to explore the full interactive mind map • Zoom, pan, and navigate
hello everyone welcome back to the channel
channel
uh in today's video we are going to
discuss a very important
interview questions this will be us most
of the times in any sql interview and we
are going to make this video fun
so this question is like
you have two tables with with the
duplicate keys and when you join them
what will be the output right
and they will ask you that okay within a
join how many numbers of the course left
join routes and full outer join and they
will try to confuse you i will cover all
the scenarios during the video i will
insert more values in these tables and
will cover all the scenarios so that
there is no confusion going forward for
all of you
okay also if you are not
aware of joint types what is left joint
inner joint full outer joint right zone
watch out my video i will tag somewhere
on the top
was that get your concept clear of four
kinds of joint and then see your video
so that it will make more sense to you
okay and please do like the video and
comment on the video so that it will
reach wider audience
okay so let's start
so i have created these two tables with
one one column and for now i have
created this
two records in this with value one one
and three chords with this with one man
one okay so
so
what will happen if we do inner join
left join right there for outer join okay
okay
i will cover more scenarios let's start
with this this video might be a long one
but please do watch so that there is no
confusion in future
so what happens when you join
these scenarios right where you have
duplicate keys so how join works so
so
when this record will go to this table i
mean this this record will go to table
t2 this is t1 t2 so this record of t1
when goes to
table t2 it will find three ones and
and
this one will not be able to
differentiate that he has to join with
which this one this one this one right
so it is not a human that it can choose
one of these it is a machine it is a
database it cannot differentiate between
these three values so it will join with
all three so this one will get joined
with all these three values and when
this one will go to this table again it
will join with all three values so there
will be six records right
right so this is how you define number
of records number of values in this the
the common keys two and here three two
into three six so if i do a inner join
there will be six values okay i will run
this later
let's talk about left and right join and
full outer join also so in left one what
is left join left join is all the
matching records will come
like the output of inner join will be
there plus the non-matching records from
left table in our case t1 is our left
table so first whatever is the output of
inner join that will be there plus
non-matching records from left table but
there is no not matching account right
all our matching records this one for
this each one there is one
one key in the other table also so there
is no non-matching records right so
so the output will be same as inner join
right similarly for right outer join all
the matching records plus
non-matching records from right table
but there is no non-matching record
again here right so in this case also
number of records will be six what is
for all to join full auto join is
matching records from both the tables
plus non-matching records again from
both table table right table also left
table also because but in both the
tables there is no non-matching code
again the result will be same as inner
join for all four joints so if i done this
this
there will be six records
or i even any join it will be six spots
right so i hope this is clear
do not get confused
okay if all our matching records in both
the tables then the result of
all the joints will be same right
right
this is a thumbdull if all are matching
records in both the tables the result of
all the four time kinds of join will be same
same okay
okay
okay now
let me add some twist in this
what i am going to do i am going to add
a value 2 here
let me execute it
so now we have a record here
non-matching record this 2 is not
matching here in t2 again 3 is not
matching here in t1
now what will happen in the inner join
only the common records will come so one
is common so there are two ones and
there are 3 verse 2 into three six records
records right
right now
now
in case of
left join what will happen in case of
red join all the matching records two
into three six records plus non-matching
records on the left table so
non-matching is two so two will come so
there will be seven records right
in case of right join
all the matching records plus
non-matching records from right table so
six plus one
how many records seven records again
okay so let me run it and then we will
go to full outer join
so two is there and null because there
is no matching records for two
from here 3 is coming and id 1 will be null
null
okay and here what will happen all the
matching records and all the non-vector
records from both the tables so there
will be 8 records right
1 1 1 for 2 null i have a record and
null 3 i have record okay so for full
auto join what is how you have to calculate
calculate
first we have to see matching records plus
plus
so there are six records so first we
will calculate by doing inner join two
into three six plus you will add
non-matching records from left table
plus non-matching the codes from right table
table
that will be the number of records in
the output similarly if you have to do
for left join
first find what will be the output
output of inertion that many number of
records plus non-matching records from
left table
i hope this is clear so far right
now let me add some more duplicate data here
here so
so
what i will do i will add one more
okay so now what is going to happen is
see one one is same now i am doing a
inner join so one one is there right and
there are three ones
so two into three how many six records
right this will remain same now for two
they are two records and how many
records are there one record of two so
two into one is two right
these are the only matching records one
and two one and two three is
non-matching so in the inner join there
will be
eight records right
right
it will be eight record see right
right
now let's let's see what will happen in
case of
left join in case of floyd join what
will happen just think about it
comment in the uh
comment in the comment section what will
be the output so if you see when we do
left join
this will be
left uh
the matching records from both tables so
one and two both are matching right and
there is no non-material caught in the
left table now right there's no
non-matching records one and two both
are matching so left join and uh inner
join will be same in this case right
there will be eight records in case of
right one what will happen now this is
this is little tricky now
what will
what how many records will be there in
right join just think about it so what
will happen now so first of all let's do
a matching record
so one is matching three into two six record
record plus
plus now
now
let's forget about 3 for now 2 is there
so 2 1 and 2
so there will be 1 into 2
there is 1 record of two here and two
records of two is there so one into two
two and two into three six and this three
three
is there a non-matching record so for
non-matching card there will be one
record always so there will be total
seven records in this case
they are
uh i think something is sorry nine
record six plus two plus one nine
records will be there okay so this this
is non-matching record and for others we
have the joins
right now let's see full outer join
at this point
for normal values i think you should be
clear how to do we will uh work with
null values after this okay so full
outer join what will happen let's
calculate full operation and what we
said first find out the
let me add one more one more value here
so that it will be it will cover
everything so
i will add a fourth here okay
okay
okay
so now let's do this
when i'm doing full auto join here let's
do first matching records so matching
chords two are there and they are there
three into two right
and let me do select
the into two plus
plus
again now we go to two how many cards
are there of two let me add one more two
okay
if you understand full joint you
understand other joints also
so three into two is fine there are two
records here three there so first we are
covering matching records what else is
matching two is matching so that two
records here two record sale of two so 2
into 2
2 into 2 plus
plus
now this is a full outer join right so
all the matching chords so we covered
now 4 is not matching so there will be
one record for this
plus there is one record three
non-matching for one record for this right
right
so one one record for not matching and
inner join so how many records are there now
now
there should be 12 records let's run it
there are 12 records you see 4 and 3 are
non-matching so other values are null
and everything else is just
just
cartesian product
right we call it cartesian product also
2 into 3 it is a cartesian 6 2 into 2 4
we call it cartesian product also ok now
last thing pending here is when we have
null values
let's cover that so i will insert some
null values
so let me insert null value here
and let me insert a null value here on
both the tables
okay now
you have to understand this null
cannot be compared to other null
what it means
null is not equal to null so null value
will not be joined also with null value
so in this case
when we are saying there is a null here
and there is a null here as well right
see null is there
okay so null is there null is there
these two nulls will not be joined
because null is not equal to null null
is a unknown value so when i do inner
join what will happen
oh my bad
so when i do inner join
only the matching records will
happen right
so matching records uh three into two
six and two into two four ten records
should be the null will not be
joined with null so there are ten
records only the matching ones
now look at the left join
so what will happen
in left join
all the records from left table will come
come so
so
null will come one null will come right
right
so let's do for this so how many records
will be there
plus
four plus
there is a one
four here
this is a non-matching record from left
table and this is also a non-matching
record so two one one for each
so there will be six plus four ten and
so
we go to left join there will be 12 records
records
because we are doing a left join so null
is coming this is not a matching null
for this null there is no matching value
so it is coming as null right
i hope this is clear now who will answer
in case of full outer join what will happen
okay so just now we saw how many records
were there
12 records right
they were total 12 records after that we added
added
so what is going to happen
this null is different from this null
so whatever we have calculated 12
records that will be there right
right and
and
there will be one one null values
separate record from both the tables
because this null is not joined with
this null so there is a separate code
for this null and there is a separate
record for this null so there will be
total 14 records now
right so let me run this
always remember see
there is this null one null and other null
null
right and there is this
uh non-matching record these are also
non-matching records and everything else
matching records
so always remember null is not equal to
null we cannot join two nulls together
we have to
separate them out that this null is
different from this null right
i hope this is clear okay let me know if
you have still have any confusion in on
this topic i can create more video but
definitely this this question will be
asked to you in the sql interview please
do like this video so that more and more
people can understand this concept and
they should not make this mistake in the
interviews okay thanks for watching have
Click on any text or timestamp to jump to that moment in the video
Share:
Most transcripts ready in under 5 seconds
One-Click Copy125+ LanguagesSearch ContentJump to Timestamps
Paste YouTube URL
Enter any YouTube video link to get the full transcript
Transcript Extraction Form
Most transcripts ready in under 5 seconds
Get Our Chrome Extension
Get transcripts instantly without leaving YouTube. Install our Chrome extension for one-click access to any video's transcript directly on the watch page.
