JOIN by NULL

Using NULLs as a "value" is highly not recommended, but sometimes there are situations, when you have to JOIN two tables and NULL value matters and have to be included in the join.
In this post I'll demonstrate how you can do it.

At first will create test tables:

USE TestDB;
GO
IF EXISTS (SELECT NULL FROM sys.tables WHERE Name = 'A')
 DROP TABLE [A];
GO
IF EXISTS (SELECT NULL FROM sys.tables WHERE Name = 'B')
 DROP TABLE [B];
GO
SELECT * INTO A 
FROM (VALUES ('0'),('1'),('2'),('3'),('4'),('5'),(''),(NULL)) x(A) ;
GO
SELECT * INTO B 
FROM (VALUES ('4'),('5'),('6'),('7'),('8'),('9'),(''),(NULL)) x(B);
GO

If you just simply join these two tables you won't get NULL values at all:

SELECT * FROM A INNER JOIN B ON A.A = B.B;

Here are two different ways to JOIN by NULL values. They look identical from SQL Server perspective. So, use the one you are comfortable with:

SELECT * 
FROM A INNER JOIN B 
ON A.A = B.B OR (A.A Is Null and B.B Is Null);
GO
SELECT * 
FROM A INNER JOIN B ON EXISTS 
(SELECT A.A INTERSECT SELECT B.B);
GO