SQL Joins Tricky Interview Questions

SQL Joins Tricky Interview Questions

Introduction

SQL Joins is always an important topic of SQL Server. In most of the SQL interviews, questions are asked about SQL Joins irrespective of the number of years experience one have. If the candidate has knowledge about SQL Joins concepts and join columns contains unique values, these SQL joins questions are not difficult to answer. But in case of some interviews, interviewers may make it trickier by asking SQL joins puzzles (queries) where these join columns may contains either duplicate values or NULL values which makes the result interesting and interview candidate confuse about outcome of these queries. Here in this article, I am going to share these SQL Joins tricky interview questions.

In this article, we are going to cover the SQL Join interview questions with answer where columns with SQL Joins condition may contain:

  1. Unique values
  2. Duplicate values
  3. Null values

Before proceeding further, I assume that you have the basic knowledge of SQL Joins concepts. I am also going to explain these SQL Joins concepts with the help of  interview questions which we are going to discuss in this article but it is better to have some prior basic knowledge of SQL Join. This below article on SQL Joins may help you in getting more information about SQL Joins concepts.

Sql Joins- Inner Joins, Self Joins, Outer Joins, Cross Joins

Tables to be use

In this article we are going to use 2 tables tbl_samplejoin1  & tbl_samplejoin2. Each of these 2 tables contains one column on which join condition has been defined. Table tbl_samplejoin1 contains the column Col1 and table tbl_samplejoin2 has the column Col2.

Interview Questions on SQL Joins

In this article, we are going to explain the impact of having the unique records or duplicate records or Null value on the outcome on the Inner Join, Left Outer Join, Right Outer Join and the Full Outer Join. We are going to use the below mentioned 4 SQL Joins queries for better understanding in each scenario of data.

a) What will the outcome of the following inner join query?

    SELECT a.Col1, b.Col2 
    FROM tbl_samplejoin1 a INNER JOIN tbl_samplejoin2 b 
    ON a.Col1= b.Col2

b) What will the outcome of the following left outer join query?

    SELECT a.Col1, b.Col2
    FROM tbl_samplejoin1 a LEFT OUTER JOIN tbl_samplejoin2 b 
    ON a.Col1= b.Col2

c) What will the outcome of the following right outer join query?

    SELECT a.Col1, b.Col2
    FROM tbl_samplejoin1 a RIGHT OUTER  JOIN tbl_samplejoin2 b 
    ON a.Col1= b.Col2

d) What will the outcome of the following full outer join query?

    SELECT a.Col1, b.Col2
    FROM tbl_samplejoin1 a FULL OUTER JOIN tbl_samplejoin2 b 
    ON a.Col1= b.Col2

Interview Scenario 1: Join Columns having Unique Values

This is the most basic and common interview scenario where interviewer asked the outcome of query containing either Inner SQL Join or Left Outer Join or Right Outer Join or the Full Outer Join using the table containing the unique records in the columns on which join is define. These questions are the basic interview questions to check interview candidate basic concepts and practical knowledge on SQL joins.

Now suppose both tables contains the unique records as shown in the below screenshot.

Lets explains the outcomes of these 4 SQL joins queries one by one

Answers with explanation

a) Answer of the first interview question containing the Inner Join

If we look the first interview question, SQL query is a Inner Join between the 2 tables. Before we going to discuss the outcome of the inner join query which is shown in the below screenshot, let refresh the concept of Inner Join.

Inner Join:- This join returns all the rows from both tables where there is a match. In other words you can say that it gives all the records of the left table which have the matching records from the right table

Now if we look at the records of both tables, there are only 2 matching records with value 1 & 2. Therefore, when we execute the inner query mentioned in the screenshot, it gives only 2 records.

b) Answer of the 2nd interview question containing the Left Outer Join

SQL query in the 2nd interview question talks about the Left outer join. let first discuss about Left outer join before we proceed towards its answer.

Left Outer Join:- This join returns all records from the left table irrespective of whether right table contains the matching records or not. For all matching records, it returns the matched records from the right table and for not matching records, it return with NULL value.

So if look at both tables data, we found that two records (1 & 2) of the table tbl_samplejoin1 has the matching records in the second table tbl_samplejoin2. But the remaining two records (3 & 5) don’t have the matching record in second table. So result-set returns records 1 & 2 of  table tbl_samplejoin1 with matching records 1 & 2 from right table and for records 3 & 5, it return with NULL value.

c) Answer of the 3rd interview question containing the Right Outer Join

Right Outer Join:- This join returns all records from the Right table irrespective of whether Left table contains the matching records or not. For all matching records from the right table, it returns the matched records from the Left table and for not matching records, it return with NULL value.

So if we look into the outcome of the query mentioned above, we see 3 rows. It because since the table on the right tbl_samplejoin2 contains 3 rows with values 1, 2 & 4. We have matching values for 1 & 2 in left table but not for 4. So first two rows of the outcome contains the matching records of value 1& 2 but for third row it has NULL from left table and 4 from Right table

d) Answer of the 4th  interview question containing the FULL Outer Join

Full Outer Join:- This Join will return all the records from the left table as well as from the right table. It return matching records from both tables. For non matching records of the left table, it return NULL value as the right table records. Similarly for non matching records of the right table, it return NULL value as the left table records.

Again in case of FULL outer join, we got 5 rows. First & second rows contains the matching records of both tables. Since there is no record in right table with values 3 & 5 so we have 3rd & 4th rows with values 3 & 5 fro left table and NULL value from right table. Now left table tbl_samplejoin1 contains no record with value 4 so we have 5th row in the outcome where we have value 4 from right table and NULL value from left table.

Interview Scenario 2: Join columns having duplicate values

Let insert value 1 into the table tbl_samplejoin2 so that this tables contains the duplicate values. So after inserting the duplicate record in table 2, both tables contains the following data .

Now if the interviewer asked any of the 4 SQL joins queries again then, duplicate record in the 2nd table may put confusion in the candidate mind. Let check how this duplicate record impact the outcomes of these queries.

a) Answer of the first question will be

With duplicate values in the Join column, we found one extra row has been added to the result-set. This is because we have now 2 matching rows in table tbl_samplejoin2 containg value “1” for the value “1” of the table tbl_samplejoin1.

b) Answer of the 2nd question will be

Again  if look at both tables data, we found that two records (1 & 2) of the table tbl_samplejoin1 has the matching records in the second table tbl_samplejoin2. But the remaining two records (3 & 5) don’t have the matching record in second table. So result-set returns values of 1 & 2 from  table tbl_samplejoin1 with matching values 1 & 2 from second table and for records 3 & 5, it return with NULL value.

c) Answer of the 3rd question will be

So if we look into the outcome of the query mentioned above, we see 4 rows. It is because the table on the right tbl_samplejoin2 contains 4  rows with values 1,1, 2 & 4. We have matching values for 1 & 2 in left table but not for 4. So first three rows of the outcome contains the matching records of value 1& 2 but for forth row it has NULL from left table and 4 from Right table

d) Answer of the 4th question will be

Now with duplicate records, in case of FULL outer join, we got 6 rows. First, second, & third rows contains the matching records of both tables. Since there is no record in right table with values 3 & 5 so we have 4th & 5thth rows with values 3 & 5 fro left table and NULL value from right table. Now left table tbl_samplejoin1 contains no record with value 4 so we have 6th row in the outcome where we have value 4 from right table and NULL value from left table.

Interview Scenario 3: One Join table contains Null Value

After adding the duplicate values, make it more interesting by adding the Null values and see its impact on the inner join and outer joins. Lets add NULL value in the 2nd table tbl_samplejoin2 also. So the data in 2nd table become

Again let see how our answers for the above 4 SQL Joins queries are going to change after one of the tables contains the  NULL value in the Id column

a) Impact on the Inner query

Here we see that there is no impact of adding NULL value in the 2nd table and we got the same result-set as we got in 2nd Interview scenario.

b) Impact on the Left Outer Join query

Since in case of Left outer join, we got all records of the left table with corresponding matching records from right table in  case of match or NULL value from right table in case of no match. So there is no impact of adding NULL value in the 2nd table tbl_samplejoin2.

c) Impact on the Right Outer join query

If we compare the result-set with the result-set we got for Right Outer Join from our second interview scenario, we found one extra row. This is because we have added one extra record of NULL value in the 2nd table tbl_samplejoin2 and there is no matching value of NULL in the 1st table. So we have 5th row with one NULL value from right table tbl_samplejoin2 and and another NULL from left table as there is no matching record of NULL value in left table.

d) Impact on the Full Outer join query

If we again compare the result-set with the result-set we got for FULL Outer Join from our second interview scenario, we found one extra row. This 7th row contains one NULL value from right table tbl_samplejoin2 and and another NULL from left table as there is no matching record of NULL value in left table.

Interview Scenario 4: Both Tables containing Null Values.

Now add NULL value in the 1st table also so that both tables contains the Null value. Now it become more interesting to know when Null value of one table try to match with another Null value of the 2nd table how it is going to affect the outcome of the same four interview join questions.

a) Impact on the Inner join

If we look at the resultset of the Inner Join query after adding Null Value in both the tables, we found that Null value has no impact on the Inner Join and we got the same 3 rows which we got earlier too.

b) Impact on the Left outer join query outcome

In case of Left outer join, we can see the impact of adding NULL value on the result-set. One extra row in the record-set with NULL value in both columns. Since NULL is treated as unknown. So it cant be compared with any value. So we have NULL value from left table and since there can’t be done any matching because of NULL, we have NULL on the column Col2.

c) Impact on the Right outer join query outcome

We can see the similar effect of adding Null value in both table as we got one extra row of NULL values in the result-set. Here we have NULL value from right table and since there can’t be done any matching because of NULL which is consider as unknown value, we have NULL on the column Col1.

d) Impact on the Full outer join query outcome

This outcome is also self explanatory as first 3 rows of the result-set are matching values. For the rows 4 & 5, since the table tbl_samplejoin2 doesn’t have the values 3 & 5 in the col2 so we have NULL value against the values 3 & 5.

For 6th row, Since table tbl_samplejoin1 contain NULL which is a unknown value which can’t be matched so we have NULL value against it in col2.

In 7th Row, we have NULL in Col1 since table tbl_samplejoin2’s col2 value 4 doesn’t find any matching value in table tbl_samplejoin1.

For 8th row, Since table tbl_samplejoin2 contain NULL which is a unknown value which can’t be matched so we have another NULL value against it in col1.

Interview Scenario 5: Join tables containing Multiple Null Values

After adding duplicate values and one Null value,  what will happen if interviewer add more null values into into the tables. To know the change in the outcome of the queries, let add one more Null value in the first table. Now both the tables contains the data as shown in the below screenshot

a) Impact on the inner join query outcome

Since there is no impact of NULL value on the Inner join query so we have no change in the result as we have in the interview scenario 2.

b) Impact on the left join query outcome

Here we have 2 NULL values in the left table tbl_samplejoin1.

So we have first 3 rows of matching values with matching values in both tables..

4 & 5 rows has values from left table but has NULL in col2 since there is no corresponding matching values in the right table.

6 & 7 rows have NULL values from left table but NULL in col2 as NULL is consider as unknown so can’t have the matching value from right table.

c) Impact on the Right join query outcome

Since we have no extra value added to the right table tbl_samplejoin2 so we have the same result-set which we have in interview scenario 4.

d) Impact on the Full outer join query outcome

Again if we see at the result-set, we find one additional row as compared to the result-set we got in Interview scenario 4. This extra row (9th) contains NULL value in both columns. NULL in the Col1 is the NULL which we inserted into the table tbl_samplejoin1. Since NULL value is considered as unknown so we have NULL value in the Right table column col2.

Summary:-

In this article, I have tried to explain the SQL Join concepts with the help of the Interview questions which interview candidate faces regularly. In this article we started interview question scenario using the table with column having join condition contains the unique values. After that we see the impact of having duplicate values in the Join columns. In the end we see the impact of  having Null values in the join columns on Inner Join, Left Outer Join, Right Outer Join and the Full Outer Join.

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Server. Bookmark the permalink.

32 Responses to SQL Joins Tricky Interview Questions

  1. Akhil Mittal says:

    Very nice article covering good interview questions. Very helpful. Thanks for sharing.

  2. Anonymous says:

    The kind of explanation you can give even to someone who can’t spell SQL. Great job!

  3. Anonymous says:

    These examples are awesome for interview.. Thanks for sharing this type of articles

  4. Anonymous says:

    Nice keep it up

  5. Anonymous says:

    very nice explanation and very useful . i faced this question and got confused in the interview 🙁

  6. Anonymous says:

    Excellent job

  7. Awesome post! Keep up the great work! 🙂

  8. Great content! Super high-quality! Keep it up! 🙂

  9. Anonymous says:

    Great job

  10. Anonymous says:

    awesome content to clear concepts on join

    • Anonymous says:

      Have got this type of scenario questions in interviews and always got confused.. Now if I get a question, I will answer with 100 percent confidence. Thanks for this valuable article

  11. Anonymous says:

    Very well Explained ! Thanks !

  12. Anonymous says:

    Very well explained the concept of joins through different scenarios.

  13. Anonymous says:

    Joins concept very clearly understood with wide range of examples given. Excellent work. Thank you 🙂

  14. Sunil says:

    Very Nice post! Keep up the great work!

  15. Anonymous says:

    Excellent explanation! Really amazing. Understood all the concepts very easily and helped a lot in my interview preperation.
    Thanks a lot! 🙂

  16. Nice article..keep posting..

  17. Anonymous says:

    This article has covered all the scenarios and which might be asked in an interview. Thank you !!

  18. Ragini says:

    Very nice and useful article

  19. Anonymous says:

    Very nice and useful article

  20. priya says:

    *** Very usfull and best one …..

  21. Anonymous says:

    really helpful, thanks a lot!

  22. Anonymous says:

    lets take a same value in both the table i.e

    Table 1 Table 2
    1 1
    1 1
    1
    1

    In this case all the joins will give same result i.e number of rows will be 8

    correct me if I am wrong .

  23. asdfhh says:

    AWESOME EXPLANATION. EASY AND COMPREHENSIVE

  24. Anonymous says:

    Too good and Thank you very much

  25. Ansh says:

    Really helpful. Thanks for posting this

  26. Anonymous says:

    Clearly explained. Thanks for the article

  27. Anonymous says:

    really helpful, Thanks a lot..!

  28. Anonymous says:

    Nice 👌. Very very helpful.

Leave a Reply