0:03 hello everyone welcome back to the channel
0:03 channel
0:06 uh in today's video we are going to
0:08 discuss a very important
0:10 interview questions this will be us most
0:12 of the times in any sql interview and we
0:14 are going to make this video fun
0:16 so this question is like
0:18 you have two tables with with the
0:20 duplicate keys and when you join them
0:23 what will be the output right
0:25 and they will ask you that okay within a
0:27 join how many numbers of the course left
0:29 join routes and full outer join and they
0:31 will try to confuse you i will cover all
0:34 the scenarios during the video i will
0:36 insert more values in these tables and
0:37 will cover all the scenarios so that
0:39 there is no confusion going forward for
0:40 all of you
0:43 okay also if you are not
0:45 aware of joint types what is left joint
0:47 inner joint full outer joint right zone
0:49 watch out my video i will tag somewhere
0:50 on the top
0:52 was that get your concept clear of four
0:54 kinds of joint and then see your video
0:56 so that it will make more sense to you
0:58 okay and please do like the video and
1:00 comment on the video so that it will
1:02 reach wider audience
1:04 okay so let's start
1:06 so i have created these two tables with
1:08 one one column and for now i have
1:09 created this
1:12 two records in this with value one one
1:13 and three chords with this with one man
1:15 one okay so
1:15 so
1:17 what will happen if we do inner join
1:19 left join right there for outer join okay
1:20 okay
1:22 i will cover more scenarios let's start
1:25 with this this video might be a long one
1:27 but please do watch so that there is no
1:29 confusion in future
1:31 so what happens when you join
1:32 these scenarios right where you have
1:35 duplicate keys so how join works so
1:36 so
1:39 when this record will go to this table i
1:42 mean this this record will go to table
1:44 t2 this is t1 t2 so this record of t1
1:46 when goes to
1:49 table t2 it will find three ones and
1:49 and
1:51 this one will not be able to
1:53 differentiate that he has to join with
1:55 which this one this one this one right
1:57 so it is not a human that it can choose
1:59 one of these it is a machine it is a
2:02 database it cannot differentiate between
2:04 these three values so it will join with
2:06 all three so this one will get joined
2:08 with all these three values and when
2:10 this one will go to this table again it
2:12 will join with all three values so there
2:14 will be six records right
2:16 right so this is how you define number
2:19 of records number of values in this the
2:22 the common keys two and here three two
2:25 into three six so if i do a inner join
2:28 there will be six values okay i will run
2:29 this later
2:31 let's talk about left and right join and
2:33 full outer join also so in left one what
2:35 is left join left join is all the
2:37 matching records will come
2:39 like the output of inner join will be
2:41 there plus the non-matching records from
2:44 left table in our case t1 is our left
2:46 table so first whatever is the output of
2:49 inner join that will be there plus
2:51 non-matching records from left table but
2:53 there is no not matching account right
2:55 all our matching records this one for
2:58 this each one there is one
3:00 one key in the other table also so there
3:03 is no non-matching records right so
3:05 so the output will be same as inner join
3:07 right similarly for right outer join all
3:10 the matching records plus
3:12 non-matching records from right table
3:13 but there is no non-matching record
3:15 again here right so in this case also
3:17 number of records will be six what is
3:20 for all to join full auto join is
3:22 matching records from both the tables
3:24 plus non-matching records again from
3:26 both table table right table also left
3:28 table also because but in both the
3:29 tables there is no non-matching code
3:32 again the result will be same as inner
3:35 join for all four joints so if i done this
3:36 this
3:38 there will be six records
3:42 or i even any join it will be six spots
3:45 right so i hope this is clear
3:46 do not get confused
3:49 okay if all our matching records in both
3:52 the tables then the result of
3:54 all the joints will be same right
3:55 right
3:57 this is a thumbdull if all are matching
4:00 records in both the tables the result of
4:02 all the four time kinds of join will be same
4:03 same okay
4:04 okay
4:06 okay now
4:08 let me add some twist in this
4:10 what i am going to do i am going to add
4:11 a value 2 here
4:18 let me execute it
4:25 so now we have a record here
4:27 non-matching record this 2 is not
4:30 matching here in t2 again 3 is not
4:33 matching here in t1
4:36 now what will happen in the inner join
4:38 only the common records will come so one
4:40 is common so there are two ones and
4:41 there are 3 verse 2 into three six records
4:43 records right
4:44 right now
4:45 now
4:47 in case of
4:49 left join what will happen in case of
4:52 red join all the matching records two
4:54 into three six records plus non-matching
4:56 records on the left table so
4:58 non-matching is two so two will come so
5:01 there will be seven records right
5:02 in case of right join
5:04 all the matching records plus
5:06 non-matching records from right table so
5:08 six plus one
5:11 how many records seven records again
5:13 okay so let me run it and then we will
5:16 go to full outer join
5:19 so two is there and null because there
5:22 is no matching records for two
5:25 from here 3 is coming and id 1 will be null
5:26 null
5:27 okay and here what will happen all the
5:29 matching records and all the non-vector
5:31 records from both the tables so there
5:33 will be 8 records right
5:36 1 1 1 for 2 null i have a record and
5:39 null 3 i have record okay so for full
5:41 auto join what is how you have to calculate
5:42 calculate
5:44 first we have to see matching records plus
5:45 plus
5:47 so there are six records so first we
5:49 will calculate by doing inner join two
5:52 into three six plus you will add
5:53 non-matching records from left table
5:55 plus non-matching the codes from right table
5:56 table
5:57 that will be the number of records in
5:59 the output similarly if you have to do
6:00 for left join
6:02 first find what will be the output
6:04 output of inertion that many number of
6:07 records plus non-matching records from
6:08 left table
6:11 i hope this is clear so far right
6:13 now let me add some more duplicate data here
6:14 here so
6:15 so
6:18 what i will do i will add one more
6:33 okay so now what is going to happen is
6:35 see one one is same now i am doing a
6:39 inner join so one one is there right and
6:40 there are three ones
6:44 so two into three how many six records
6:47 right this will remain same now for two
6:48 they are two records and how many
6:51 records are there one record of two so
6:53 two into one is two right
6:55 these are the only matching records one
6:57 and two one and two three is
6:59 non-matching so in the inner join there
7:00 will be
7:01 eight records right
7:02 right
7:05 it will be eight record see right
7:06 right
7:08 now let's let's see what will happen in
7:10 case of
7:13 left join in case of floyd join what
7:14 will happen just think about it
7:16 comment in the uh
7:18 comment in the comment section what will
7:20 be the output so if you see when we do
7:22 left join
7:23 this will be
7:24 left uh
7:27 the matching records from both tables so
7:30 one and two both are matching right and
7:31 there is no non-material caught in the
7:33 left table now right there's no
7:35 non-matching records one and two both
7:37 are matching so left join and uh inner
7:39 join will be same in this case right
7:41 there will be eight records in case of
7:43 right one what will happen now this is
7:45 this is little tricky now
7:46 what will
7:47 what how many records will be there in
7:50 right join just think about it so what
7:52 will happen now so first of all let's do
7:54 a matching record
7:56 so one is matching three into two six record
7:58 record plus
7:59 plus now
8:00 now
8:02 let's forget about 3 for now 2 is there
8:04 so 2 1 and 2
8:06 so there will be 1 into 2
8:11 there is 1 record of two here and two
8:13 records of two is there so one into two
8:16 two and two into three six and this three
8:17 three
8:19 is there a non-matching record so for
8:21 non-matching card there will be one
8:23 record always so there will be total
8:26 seven records in this case
8:28 they are
8:31 uh i think something is sorry nine
8:32 record six plus two plus one nine
8:35 records will be there okay so this this
8:37 is non-matching record and for others we
8:39 have the joins
8:41 right now let's see full outer join
8:43 at this point
8:45 for normal values i think you should be
8:47 clear how to do we will uh work with
8:50 null values after this okay so full
8:52 outer join what will happen let's
8:53 calculate full operation and what we
8:56 said first find out the
8:58 let me add one more one more value here
9:00 so that it will be it will cover
9:02 everything so
9:05 i will add a fourth here okay
9:06 okay
9:15 okay
9:18 so now let's do this
9:21 when i'm doing full auto join here let's
9:23 do first matching records so matching
9:25 chords two are there and they are there
9:28 three into two right
9:30 and let me do select
9:32 the into two plus
9:35 plus
9:37 again now we go to two how many cards
9:40 are there of two let me add one more two
9:48 okay
9:49 if you understand full joint you
9:52 understand other joints also
9:54 so three into two is fine there are two
9:56 records here three there so first we are
9:58 covering matching records what else is
10:00 matching two is matching so that two
10:03 records here two record sale of two so 2
10:04 into 2
10:06 2 into 2 plus
10:06 plus
10:09 now this is a full outer join right so
10:12 all the matching chords so we covered
10:14 now 4 is not matching so there will be
10:16 one record for this
10:19 plus there is one record three
10:22 non-matching for one record for this right
10:23 right
10:25 so one one record for not matching and
10:27 inner join so how many records are there now
10:28 now
10:33 there should be 12 records let's run it
10:35 there are 12 records you see 4 and 3 are
10:37 non-matching so other values are null
10:39 and everything else is just
10:40 just
10:42 cartesian product
10:44 right we call it cartesian product also
10:48 2 into 3 it is a cartesian 6 2 into 2 4
10:50 we call it cartesian product also ok now
10:52 last thing pending here is when we have
10:54 null values
10:56 let's cover that so i will insert some
10:57 null values
11:00 so let me insert null value here
11:03 and let me insert a null value here on
11:05 both the tables
11:07 okay now
11:09 you have to understand this null
11:11 cannot be compared to other null
11:13 what it means
11:16 null is not equal to null so null value
11:18 will not be joined also with null value
11:20 so in this case
11:23 when we are saying there is a null here
11:26 and there is a null here as well right
11:29 see null is there
11:31 okay so null is there null is there
11:33 these two nulls will not be joined
11:34 because null is not equal to null null
11:37 is a unknown value so when i do inner
11:39 join what will happen
11:41 oh my bad
11:43 so when i do inner join
11:46 only the matching records will
11:47 happen right
11:50 so matching records uh three into two
11:52 six and two into two four ten records
11:54 should be the null will not be
11:56 joined with null so there are ten
11:58 records only the matching ones
12:01 now look at the left join
12:02 so what will happen
12:04 in left join
12:06 all the records from left table will come
12:07 come so
12:08 so
12:10 null will come one null will come right
12:11 right
12:14 so let's do for this so how many records
12:15 will be there
12:21 plus
12:29 four plus
12:31 there is a one
12:33 four here
12:35 this is a non-matching record from left
12:36 table and this is also a non-matching
12:39 record so two one one for each
12:42 so there will be six plus four ten and
12:49 so
12:50 we go to left join there will be 12 records
12:52 records
12:55 because we are doing a left join so null
12:58 is coming this is not a matching null
13:00 for this null there is no matching value
13:02 so it is coming as null right
13:04 i hope this is clear now who will answer
13:06 in case of full outer join what will happen
13:12 okay so just now we saw how many records
13:14 were there
13:20 12 records right
13:23 they were total 12 records after that we added
13:24 added
13:29 so what is going to happen
13:31 this null is different from this null
13:34 so whatever we have calculated 12
13:36 records that will be there right
13:37 right and
13:38 and
13:41 there will be one one null values
13:43 separate record from both the tables
13:45 because this null is not joined with
13:47 this null so there is a separate code
13:49 for this null and there is a separate
13:51 record for this null so there will be
13:55 total 14 records now
13:58 right so let me run this
14:00 always remember see
14:02 there is this null one null and other null
14:04 null
14:05 right and there is this
14:08 uh non-matching record these are also
14:09 non-matching records and everything else
14:12 matching records
14:14 so always remember null is not equal to
14:18 null we cannot join two nulls together
14:19 we have to
14:21 separate them out that this null is
14:23 different from this null right
14:26 i hope this is clear okay let me know if
14:28 you have still have any confusion in on
14:30 this topic i can create more video but
14:32 definitely this this question will be
14:35 asked to you in the sql interview please
14:37 do like this video so that more and more
14:39 people can understand this concept and
14:41 they should not make this mistake in the
14:44 interviews okay thanks for watching have
