Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
You can use ISNULL or COALESCE –  DavidG Jul 22 at 16:32
    
SQL is just that (regarding Nulls.) Ugly and illogical. To be exact it uses a different ternary logic than the usual one (with only 2 values, TRUE and FALSE). Keep the (column <> 26 OR column IS NULL) checks, don't use COALESCE() or ISNULL() for that. –  ypercube Jul 22 at 16:35
    
If you really want to avoid all these, just make your columns NOT NULL. Problem solved. –  ypercube Jul 22 at 16:37
2  
What I don't understand is: If the queries are built dynamically by some program/script, why do you care for their looks? –  ypercube Jul 22 at 17:16
3  
NULL does not mean "nothing" as you say. It is an undetermined value or no value is present. If the column in question is age and your age is 26 but you did not provide an age then how can you say that age is not 26? The value was not provided therefore you can't say that it isn't 26. It is unknown. –  Sean Lange Jul 22 at 18:18

2 Answers 2

up vote 7 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
"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... –  DMason Jul 22 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 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? –  DMason Jul 23 at 2:34
    
@DMason I respect yours. –  usr Jul 23 at 10:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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