Null is not a number. This is not news to anyone who has done even a little programming. It is a placeholder that essentially says, “This variable does not have a value assigned to it.” This is why the various programming languages and scripting environments that we use have methods, functions, and/or operators to test whether ​a variable is null or not. We can use these tools to handle null values one way and variables that are assigned actual values another way. This is about as basic as programming gets.

And yet it can still catch experienced programmers off-guard.

Take for example the following SQL script where @OrderDate is a parameter passed to the query:

select c.id, c.lastname, c.firstname

from customers c

            left join orders o

                        on o.customerid = c.id

where @OrderDate = o.OrderDate

The problem should be pretty easy to spot especially given the article topic. What if the passed parameter @OrderDate is null? What if o.OrderDate is null? You might be tempted to think that if both values are null, the expression would evaluate to true and any rows with an OrderDate set to null would be returned. And you might be right. In Microsoft’s SQL Server, the answer depends on which settings you have enabled as documented in this MSDN article on the equals operator:

So if ANSI_NULLS is OFF, you are safe. But what if it is not? Why even guess? If you want to be able to pass a null value for your @OrderDate argument to return all rows where the OrderDate has not been set, it would be better to do it like this:

select c.id, c.lastname, c.firstname

from customers c

            left join orders o

                        on o.customerid = c.id

where (@OrderDate = o.OrderDate)

            or (@OrderDate is null and o.OrderDate is null)


​Still pretty basic stuff, right? Where this gets tougher is when the queries, methods, or functions we write get bigger and more involved. Handling null conditions like this is the sort of thing that can easily get buried in code and missed if you are not careful. Worse – missing null conditions like this has a nasty habit of looking great in tests with limited data, but failing intermittently in production. These are the kinds of bugs that can be incredibly difficult to trace. Being mindful of every place we might have to handle null values is a vital part of keeping the code we write stable and working properly.​​​​​

Tech Talk Live Blog Comment Guidelines:

One of our main goals at Tech Talk Live is to build a community. It is our hope that this blog can be a forum for discussion around our content. We see commenting as an integral part of this community. It allows everyone to participate, contribute, connect, and share relevant personal experience that adds value to the conversation. Respect counts. We believe you can disagree without being disagreeable. Please refrain from personal attacks, name calling, libel/defamation, hate speech, discriminatory or obscene/profane language, etc. Comments should keep to the topic at hand, and not be promotional or commercial in nature. Please do not link to personal blog posts, websites, or social media accounts that are irrelevant to the conversation. This is considered self-promotion. We welcome links that help further the conversation and reserve the right to delete those we deem unnecessary. The appearance of external links on this site does not constitute official endorsement on behalf of Tech Talk Live or Lancaster-Lebanon Intermediate Unit 13. You are solely responsible for the content that you post – please use your best judgment. We reserve the right to remove posts that do not follow these guidelines.