Most Asked SQL JOIN based Interview Question | # of Records after 4 types of JOINs

00:14:45
https://www.youtube.com/watch?v=xR87ctOgpAE

抂芁

TLDRThis video tutorial addresses a common SQL interview question concerning join operations, focusing on scenarios involving tables with duplicate keys. The instructor explains various join types, such as inner join, left join, right join, and full outer join, and how they behave when duplicate keys are present. The video provides a comprehensive analysis of how joins work, including the distinction between matching and non-matching records. Special attention is given to situations involving null values, emphasizing that nulls do not join with other nulls, and discussing the concept of Cartesian products in joins. The goal is to ensure viewers understand these join types to effectively tackle similar interview questions, thereby eliminating confusion and improving their SQL skills.

収穫

  • 🀔 Understand how duplicate keys affect SQL join operations.
  • 🔄 Inner joins create Cartesian products with duplicate keys.
  • 🧩 Left joins include non-matched records from the left table.
  • 🔍 Right joins include non-matched records from the right table.
  • 🌐 Full outer joins combine all records, including non-matches.
  • 🚫 Null values do not join with other nulls.
  • 🔑 Key matching drives the outcome of join operations.
  • 📊 Cartesian products involve all combinations of matched records.
  • 🧮 Calculating join outputs can involve complex record counting.
  • 🧠 Clear understanding aids in avoiding confusion during interviews.

タむムラむン

  • 00:00:00 - 00:05:00

    In this segment, the presenter introduces a common SQL interview question regarding handling duplicate keys in two tables and the impact of different types of joins—inner, left, right, and full outer joins. The scenario involves two tables with duplicate keys, and the video aims to clarify confusion around the number of resulting records. They explain the basics of join operations, and how the inner join works by matching all possible combinations when duplicate keys exist. The presenter emphasizes the importance of understanding these join operations to avoid confusion in SQL interviews.

  • 00:05:00 - 00:14:45

    Further, the presenter introduces non-matching records to illustrate differences between joins better. They modify one table to include an additional non-matching key and discuss how each join type (inner, left, right, full outer) handles these scenarios differently, resulting in varying numbers of records. With examples of specific data modifications, the presenter shows that left join results include non-matching records from the left table, and right join results include non-matching records from the right table, while the full outer join combines all records, accounting for each scenario. The discussion also touches upon the concept of null values, highlighting that nulls are not equal to each other, which affects how joins involving nulls are computed.

マむンドマップ

Mind Map

よくある質問

  • What are the common types of SQL joins?

    The common types of SQL joins are inner join, left join, right join, and full outer join.

  • How are duplicate keys handled in SQL joins?

    Duplicate keys are handled by joining each matching record from one table with each matching record from the other table, often resulting in a Cartesian product of the matched entries.

  • What happens when null values are included in an SQL join?

    Null values are not equal to each other, so they do not join with each other. They are considered as non-matching records during join operations.

  • How does inner join work with duplicate keys?

    With duplicate keys, an inner join creates a Cartesian product, joining each matching pair which results in multiple records.

  • What is demonstrated about non-matching records in a left join?

    In a left join, non-matching records from the left table will be included in the result set with nulls for columns from the right table.

  • What is a full outer join?

    A full outer join combines all matching records from both tables and also includes all non-matching records from both tables.

  • How many records result from a left join with non-matching entries?

    The result includes all records from the left table (including non-matching ones); the number depends on the specific dataset configuration.

  • Can nulls in different tables be joined?

    No, nulls are not considered equal to each other and hence cannot be joined.

  • What should one know about Cartesian products in joins?

    Cartesian products occur when each record in one table is joined with every record in the other due to the conditions not sufficiently narrowing down records.

  • Why should you not be confused about SQL joins during interviews?

    Understanding the conditions for matching keys and how various types of joins operate can help systematically approach these questions, eliminating confusion.

ビデオをもっず芋る

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