Joining Tables That Contain NULL Values
Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Saint Augustine, FL. Bradley is a former consultant, trainer, and has coauthored 6 SQL Server and Power BI books, most recently Learn Microsoft Fabric. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. Bradley gives back to the community through speaking at events such as the SQLBits, Fabric Community Conference, PASS Community Data Summit, SQL Saturdays, Code Camps, and user groups across the country including locally at the Jacksonville SQL Server User Group (JSSUG). He is a contributor on SQLServerCentral.com and blogs on his personal site, BradleySchacht.com.
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.