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.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook