How do I include nulls during comparisons in SQL Server?

Question

So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results.

For example, the following query doesn't return rows where value IS NULL but I want it to:

SELECT *
FROM table
WHERE
  (value != 26)
  AND date IS NULL
  AND last_modified >= '5/21/2014'

I understand I can do the following as a workaround but seriously? Having to add parentheses and check if is null for every single field every single time I want to include it? Seems ugly, not intuitive and crazy.

SELECT *
FROM table
WHERE
  (value != 26 OR value is null)
  AND date IS NULL
  AND last_modified >= '5/21/2014'

I mean I know NULL is not a value and thus can't be compared but can't you infer that it is, in fact, definitely not 26? If 26 is something and NULL is nothing and nothing is not something then NULL is not 26. Seems logical to me.

Does anyone know how I can, in a cleaner way, include nulls in my results when using comparisons without having to include an explicit check each and every time. Also turning off nulls on my tables is definitely not an option.

Edit:

My real problem of why I don't want to do it the way I showed was not exposed. So here goes. I am writing a program that let's you build queries to database tables and let's the user dynamically create filters and such which essentially at the end of the day constructs a SQL statement and gets the results to display to the user. The fields chosen by the user can be any and/or all fields in a given database and if I have to literally put that ISNULL check on every single field that would be really inefficient and make looking at the SQL super ugly. My program is table-definition-agnostic meaning I don't care what's in your table and don't want to know what your table's definition is. I just want you to pick a table, choose some fields to filter on with equals, not equals, in, not in, etc ... and then click a button to view the results of that request.

Answer 1

To start off, NULL does not mean "no value" it means "Unknown value" in SQL Server. There is a session setting called ANSI_NULLS that could make your queries behave as you would like them to, however, it's deprecated and will be forced to ON (which you don't seem to like) in a future version: http://msdn.microsoft.com/en-us/library/ms188048.aspx

I get what you're trying to do, and to make a counter point I would ask if you've seen any queries generated by reporting services or something like Cognos? If so, you'll see exactly what you're describing you don't want to do. Why? Well because with a schema that allows nulls, that's the way to do it. I'm not saying it's a super awesome and great idea but it works all of the time.

What your designer could do is check to see if that column could even be null and if so the appropriate logic could step it and create the correct query. You could also have options such as "This column may be null, do you want those values?". I don't know the end-game per-se and writing your own dynamic querying tool is quite the deat when the logical consistencies are all factored in (such as this).

I would continue to do explicit null checking on columns that could possibly be null, sure it doesn't look the best but it works all of the time.

ANSI_NULL set option will work for now but NOT a good idea especially if you don't control the environment, plus it will be forced set ON later and cause errors where you'll need to re-write your application logic anyway. This is the way SQL Server works with NULLs.

Answer 2

The NULL problem is a thorny issue with SQL. It is basically a mistake that is now burnt into all SQL software on the planet. We have to deal with it.

value <> 26 or value is null is a good way to implement this logic. There are other formulations of the same semantics.

If you know that value is never -1 (for example) you can say ISNULL(value, -1) <> 26. I don't think that's better from a readability standpoint. It can also cause optimizer problems because this predicate might not be SARGable. value is null is a SARGable and indexable predicate contrary to popular belief.

SQL has the IS DINSTINCT FROM operator but T-SQL does not support it. Please take a second to vote for the request to have it implemented! It is a purely syntactic issue. The optimizer does not need to change at all. It already supports that operator internally.

TL;DR: The way you are doing it right now is the right way. Live with it.