1

So basically in SQLServer 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.

| |

Browse other questions tagged or ask your own question.