7

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.

  • I don't have control over the table, it is a legacy table and I'm writing code that uses it. – Brian T Hannan Jul 22 '14 at 16:38
  • Re. "Having to add parentheses and ...? Seems ugly, not intuitive and crazy.": You should - ALWAYS "add parentheses". "Code is read much more than it is written." Don't make every (mistake-prone) human reader have to a) know the precedence, b) remember it and c) apply it correctly every time they read every Expression, and therefore violate the DRY Principle much more often (than you would by adding "'unneeded'" parentheses). "Just cuz you cannnn (leave them out), don't mean you shouuuld (leave them out)..." – Tom Sep 6 '18 at 18:48
  • This Question is essentially a duplicate of the StackOverflow Question from 8 months prior that had a much better answer here (which itself was based on an article 2 years prior to it): stackoverflow.com/questions/19682956/… – Tom Sep 6 '18 at 20:43
8

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.

6

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.

  • 1
    "Basically a mistake". I see this differently. The relational model is based on set theory and predicate logic. SQL implements three-valued predicate logic by supporting NULL to signify the generic concept of a missing value. Support for NULLs and three-valued predicate logic has been part of the ANSI and ISO standards for a long time. Sadly, debate and confusion reigns... – Dave Mason Jul 22 '14 at 16:50
  • 2
    @DMason I understand the reasoning behind that model (although it has nothing to do with sets. This is a scalar issue.). It's just that nobody wants that model. It is not useful in practice. Seemed like a good idea at the time but 30 years later we know that it wasn't one. There's a reason almost all programming languages treat null like any other value. C# with nullable value types does that as well. – usr Jul 22 '14 at 16:53
  • Controversial? Indeed! You are not alone, @usr. Many others think a valid relational model should follow two-valued logic, and strongly object to the concept of NULLs in SQL. E.F. Codd, the creator of the relational model believed in the idea of supporting missing values and predicates that extend beyond two-valued logic. I happen to agree with him. You have a different opinion. C'est la vie. Who am I to deny you your opinion? – Dave Mason Jul 23 '14 at 2:34
  • @DMason I respect yours. – usr Jul 23 '14 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.