Scenario 4: Both Tables contain Null Values.
Now we will add the NULL value in the 1st table also so that both tables contain the Null value. Now it will become more interesting to know when the Null value of one table tries to match with another Null value of the 2nd table then how it is going to affect the result of the joins.
a) Inner Join: If we look at the result of the Inner Join 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, and if we have to include the NULL values in the result then we have to use the NULL safe join operator <=>, but that is a different topic, for now, we ignore this.
b) Left Outer join: In the case of Left outer join, we will see the impact of adding NULL value in the result-set. One extra row in the result-set with a 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 the left table and since there can’t be any matching because of NULL, so we will have NULL on the column Col2 also.
c) Right Outer Join: In the case of Right outer join, we will see the similar effect of adding Null value in both tables as we got one extra row of NULL values in the result-set. Here we have NULL value from the right table and since there can’t be any matching because of NULL which is considered an unknown value, we will have NULL on the column Col1.
d) Full outer join: The result of Full outer join is self-explanatory as the 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 the 6th row, Since table tbl_samplejoin1 contain NULL which is an unknown value that can’t be matched so we have NULL value against it in col2.
For the 8th row, Since table tbl_samplejoin2 contains NULL which is an unknown value that can’t be matched so we have another NULL value against it in col1.
Scenario 5: Join tables containing Multiple Null Values.
let's add one more Null value to the first table. Now both the tables contain the data as shown in the below screenshot:
a) Inner Join: 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 scenario 2.
b) Left Outer Join: Here we have 2 NULL values in the left table tbl_samplejoin1.So we have the first 3 rows of matching values with matching values in both tables. 4 & 5 rows have values from the left table but have NULL in col2 since there are no corresponding matching values in the right table. 6 & 7 rows have NULL values from the left table but NULL in col2 as NULL is considered unknown so can’t have the matching value from the right table.
c) Right Outer Join: Since we have no extra value added to the right table tbl_samplejoin2 so we will have the same result-set which we have in the last scenario.
d) Full Outer Join: In the case of FULL outer join we will have one extra row for duplicate Null values in tbl_samplejoin1.This extra row (9th) contains the NULL value in both columns. NULL in Col1 is the NULL that we inserted into the table tbl_samplejoin1. Since the NULL value is considered unknown so we have the NULL value in the Right table column col2.
Note: If we want to include the NULL values in the result then we have to use the NULL safe join operator <=>, but that is a different topic, for now, we ignore this. We will cover this NULL safe join operator later.
