As you can see we have two tables, with the same values.
Joining on these tables is no problem at all. Simple, right? Well, take a look at what happens when we try to join on the column containing the NULL values.
The results are the following:
As you can see, we are missing a row. The row containing the NULL values did not get picked up by our join. We should have all three rows in the result set. The join statement does not deal with NULL values well when joining. So we can use ISNULL to replace the NULL values with something else. How about if we just replace the NULLs with an empty space.
The results are even worse when running this query.
Since we are only joining on Column2 anytime there is an NULL or empty space the two tables are joined. This is why we get the second and fourth rows back. The reason we get NULL back in the table is because we are returning the actual table values, NULL in those cases, and only replacing the NULL with a blank space for the join; we are not actually altering the table at all. The solution is to replace the NULL values with something that is not going to appear anywhere else in our table.
Finally! The results we want.
The long and the short of it is, if you want to do joins on columns that can contain NULL values, be ready for some suprising results. Make sure you always build in some logic that will prevent the crazy results like those seen above. Happy joining.
