SQL Server – Best way to Handle NULLS in Joins

Well today we will talk about how to handle null values while joining tables, especially in case of inner joins when you want them to match. If you see online there some methods given but none of them mentioned which one is the best. It actually depends on the kind of query and the kind of data you have in your tables. I will show you some methods you can use to join null values while joining tables.

Let’s create some sample data.

--

CREATE TABLE testJoinswithNULLs1
(
	ID INT 
)
GO

INSERT INTO testJoinswithNULLs1 VALUES ( NULL )

CREATE CLUSTERED INDEX Ix_Id ON testJoinswithNULLs1(ID) 

CREATE TABLE testJoinswithNULLs2
(
	ID INT 
)
GO

INSERT INTO testJoinswithNULLs2 VALUES ( NULL )
GO 1000

INSERT INTO testJoinswithNULLs2 VALUES (1),(2),(3),(4),(5)

CREATE CLUSTERED INDEX Ix_Id ON testJoinswithNULLs2(ID)

--

Here both the tables have clustered keys associated with it. In first table we have a single null value and in the second table we have 1000 null values.

Method 1. Use of ISNULL function – Here we just capturing the NULL value using ISNULL() function @ run-time and replacing it by -1, -1 is just and example, You can use any value you want. The value you select should not be present in the table you are joining.

--

SELECT 
	a.ID , b.ID 
FROM 
	testJoinswithNULLs1 a INNER JOIN testJoinswithNULLs2 b
	ON ISNULL(a.ID,-1) = ISNULL(b.ID,-1)
GO

--

Method 2. Use of COALESCE function – Here we just capturing the NULL value using COALESCE() function @ run-time and replacing it by -1, -1 is just and example, You can use any value you want. The value you select should not be present in the table you are joining.

--


SELECT 
	a.ID , b.ID 
FROM 
	testJoinswithNULLs1 a INNER JOIN testJoinswithNULLs2 b
	ON COALESCE(a.ID,-1) = COALESCE(b.ID,-1)
GO

--

Method 3. Check for NULL while Joining – Here we are checking for NULL Values using OR condition

--

SELECT 
	a.ID , b.ID 
FROM 
	testJoinswithNULLs1 a INNER JOIN testJoinswithNULLs2 b
	ON a.ID = a.ID OR (a.ID IS NULL AND b.ID IS NULL)
GO

--

Now let’s check out the execution plans for all the three methods.

Now I know that the statistics are up to date, hence we can trust the cost. It is clearly evident that the last method is the best one we have. So choose wisely.:)

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com