Handling Null Values in WHERE clauses

Home » SQL and Databases » Handling Null Values in WHERE clauses
Photograph of a SSD Drive Array

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:

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:

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).

About Phil

I have been working as a software developer since 1983. This blog could have been called "From Fortran 77, C and Cobol to C# in 20 (not so) easy years", but it doesn't sound quite right somehow. Besides I'm talking about what's happened since 2003, not before!

Leave a Reply

Your email address will not be published.


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">




Top Posts & Pages

Categories

Recent Posts

Recent Comments

Archives

Blogroll

  • Mike Cohn's Blog
  • Scott Hanselman's Blog
- mike@mountaingoatsoftware.com

I wrote 25 blog posts during 2018. In case you missed some of them, here are the most popular. [...]

- mike@mountaingoatsoftware.com

The iterative and incremental nature of agile makes an agile approach seemingly less compatible with [...]

- mike@mountaingoatsoftware.com

Velocity can be great for predicting how much a team can deliver in a given period. But it needs to [...]

- mike@mountaingoatsoftware.com

Succeeding with agile isn’t just about knowing where to start, it’s about knowing where to go next—w [...]

- mike@mountaingoatsoftware.com

Here’s what to do when facing a complex user story that cannot be split but is too large for one spr [...]

- Scott Hanselman

As I said on social media today, it's 2019 and I'm updating the Firmware on a Zune, fight [...]

- Scott Hanselman

I'm going to try to finished my Relationship Hacks book in 2019. I've been sitting on it t [...]

- Scott Hanselman

My son and I were working on an Adafruit NeoTrellis M4 Mainboard over the holidays. This amazing lit [...]

- Scott Hanselman

I'm on vacation for the holidays and I'm finally getting some time to play video games. I [...]

- Scott Hanselman

I blogged about DOSBox five years ago! Apparently I get nostalgic around this time of year when I [...]

Meta