Handling Null Values in WHERE clauses

I was speaking with one of the people I am mentoring at the moment, and he turned round and said “You are clever, you always know the answer, even if you have to work it out”, to which I replied “I’m not clever, I’m just old and I know to keep up”.

This was in response to what I thought was something quite simple, that I thought all SQL developers knew, so I thought I’d share it with you.

We were working on a SQL/Server stored procedure with a T-SQL merge statement to populate certain fields in a temp table:

MERGE INTO #temp2 AS t
USING
(
	SELECT a.ApplicationCourseID, a.ApplicationID, a.SessionStaffID, a.SessionTime, cl.InterestedCourses
	FROM #temp2 a
	INNER JOIN dbo.Course cl ON cl.StudentDetailID = a.StudentDetailID
) AS s
ON t.ApplicationID = s.ApplicationID and t.ApplicationCourseID = s.ApplicationCourseID and t.SessionStaffID = s.SessionStaffID AND t.SessionTime = s.SessionTime
WHEN MATCHED THEN
UPDATE SET t.CourseCode = s.InterestedCourses;

The problem is that SessionStaffID and also SessionTime could contain null values. If one side of the equals contained a null value then this would cause the row not to be selected.

The solution was to use ISNULL (although care should be taken because putting a function on the left hand side of an equals would not allow SQL/Server to use an index). You simply choose a value that is not in the data to put as the second parameter to ISNULL like so:

MERGE INTO #temp2 AS t
USING
(
	SELECT a.ApplicationCourseID, a.ApplicationID, a.SessionStaffID, a.SessionTime, cl.InterestedCourses
	FROM #temp2 a
	INNER JOIN dbo.Course cl ON cl.StudentDetailID = a.StudentDetailID
) AS s
ON t.ApplicationID = s.ApplicationID and t.ApplicationCourseID = s.ApplicationCourseID and ISNULL(t.SessionStaffID, 0) = ISNULL(s.SessionStaffID, 0) AND ISNULL(t.SessionTime,'1900/01/01') = ISNULL(s.SessionTime, '1900/01/01')
WHEN MATCHED THEN
UPDATE SET t.CourseCode = s.InterestedCourses;

This pattern also works on ordinary WHERE clauses as well, although as I said its important to use it carefully as putting functions on the left hand side of equals does not allow SQL/Server to use an index.

(Please accept my apologies if the above T-SQL is inconsistent in some way – I have changed the column and table names for privacy purposes. I hope it gets the point across though).