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:
- Unique values
- Duplicate values
- 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.
