SQL Server INNER JOIN on NULL Columns

Le us quickly see what happens when you are joining two tables and have NULL values on both sides of a join.

Here’s a sample script:

CREATE TABLE TBL1 (Column1 NVARCHAR(50) NULL);
CREATE TABLE TBL2 (Column1 NVARCHAR(50) NULL);
GO
INSERT INTO TBL1
VALUES (NULL), ('Test1'), ('Test2');

INSERT INTO TBL2
VALUES (NULL), ('Test1'), (NULL);
GO

We will now do an INNER JOIN on the two columns:

SELECT t1.Column1, t2.Column1
FROM TBL1 t1 INNER JOIN TBL2 t2
ON t1.Column1 = t2.Column1

Whenever SQL Server evaluates the JOIN condition, the equals (=) operator returns False if any of the rows is NULL. Hence only one row is returned from the query since both the Test1 values are non-NULL and equal.