28

Let's say you have an application that has a boolean field in its User table called Inactive.

Is there anything inherently wrong with just storing false as null? If so can you please explain what the down side should be? I have discussed this with someone a few months ago and we both agreed it shouldn't matter as long as you do it consistently throughout the app/database. Recently, someone I know was emphatic that "true" true or false should be used, but they didn't really give an explanation as to why.

Improve this question
8
  • 27
    Wikipedia says Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database This is the accepted wisdom and you shouldn't redefine what Null means in your application. It will be confusing to everybody else working with your code.
    – PersonalNexus
    Feb 6, 2012 at 20:03
  • 10
    Why would you even WANT to do this? Why not just use a non-nullable bit field and set the default to false if that is the behavior you want instead of confusing the issue with a tri-state field?
    – JohnFx
    Feb 6, 2012 at 20:04
  • 5
    Real-world example of why that's a very bad idea: SELECT * FROM foo WHERE bar = FALSE doesn't give the results you expect.
    – Blrfl
    Feb 6, 2012 at 20:16
  • 2
    Consider using an int column with a default of 0 rather than boolean. This way if new conditions arise (perhaps a 'pending' state, for example), you dont have to alter the database structure.
    – GrandmasterB
    Feb 6, 2012 at 22:08
  • 4
    But if you store false as null how are you going to store FILE_NOT_FOUND?! (thedailywtf.com/Articles/What_Is_Truth_0x3f_.aspx)
    – Ed James
    Feb 6, 2012 at 22:41

4 Answers 4

Reset to default
57

Is there anything inherently wrong with just storing false as null?

Yes.

If so can you please explain what the down side should be?

NULL is not the same as False.

By definition, comparisons (and logic) that involve NULL should return values of NULL (not False). However, SQL implementations can vary.

True and NULL is NULL (not False).

True and NULL or False is NULL (not False).

http://en.wikipedia.org/wiki/Null_(SQL)#Three-valued_logic_.283VL.29

http://technet.microsoft.com/en-us/library/cc966426.aspx

Improve this answer
11
  • 3
    Succint explanation on why NULL is the value representing the absence of value.
    – maple_shaft ♦
    Feb 6, 2012 at 20:08
  • 2
    the first day of my first development job involved debugging and fixing an error like in this question/answer, reminiscence +1
    – tsundoku
    Feb 6, 2012 at 20:24
  • 1
    Note that the very article you linked to says that if the null is irrelevant to the logic (as is the case in whatever OR TRUE or whatever AND FALSE, where no value of whatever can change the condition), then the expression returns a value. It's not an optimization; it's how 3-valued logic works. Any DBMS that insists on returning UNKNOWN/NULL for those expressions is fundamentally broken.
    – cHao
    Feb 6, 2012 at 23:42
  • 1
    @S.Lott, but doesn't storing null instead of false save some space??
    – azerafati
    May 18, 2014 at 13:09
  • 2
    @Bludream: For booleans, a nullable field can actually take more space, depending on the DBMS. (It's a negligible amount in nearly all cases, though.) A boolean can be represented in a single bit...but a nullable boolean has three possible values (true, false, and null), and thus needs more than one bit.
    – cHao
    Jul 11, 2014 at 15:13
18

By allowing nulls in a boolean field, you are turning an intended binary representation (true/false) into a tri-state representation (true, false, null) where your 'null' entries are indeterminate. The 'null' value is neither appropriately 'true' nor 'false.' What reason would you have to augment your representation to be inaccurate?

Even if you decide on a pattern such as this and do it consistently throughout your application, that does not make it okay. You'll end up in a situation where it is not clear to fresh eyes why that pattern is in place or, more likely, you'll end up in a situation where that pattern is inadvertently broken.

Improve this answer
6

What others have said. 3 possible values is not a boolean.

But you may have a legitimate need for 3 values. Such as (true, false, unknown). Even if this is the case, if you are into ultra-normalization you will not allow any null values at all. Instead you'll store a true boolean in another table with a 1 to 1 relation. A null value could be produced in a query by a "failed" outer join, not by a physically stored null value.

Improve this answer
2
  • outside the realm of my boolean question why would true false unknown be bad if you used null to do it? Beyond my question are nulls generally to be avoided? Why?
    – Ominus
    Feb 7, 2012 at 0:51
  • 3
    @Ominus: Nulls are generally to be avoided if you're a purist. If used as they were meant to be used (as "there's no value here" rather than as a fakey-fake false), then they have their place. If they didn't, they wouldn't exist. The alternative, as mentioned, is basically having a whole other table with just the primary key of your row and a single boolean (or int, or varchar, or what have you). For each and every field that you'd otherwise make nullable. While relationally pure, it's too convoluted for most purposes.
    – cHao
    Feb 7, 2012 at 3:20
-3

Is bool? a boolean type? No. It is of type Nullable<T> where T is boolean. A nullable boolean can have 3 values: true, false and null. To use bool or bool? depends on your requirement. There could be legitimate reason where you may need to use null but a type that smells like binary but you do not know the answer. In above example, User.IsActive seems clear cut. User is active or not. As a system, it might want to know if a user is active or not. There should not be 'maybe'. But think about something like a feature flag. Is the feature turned on? The answers could be yes, no, or not sure. You may have a business rule that spells out only show the button when flag is set to true. One may argue bool is by default false so why create nullable bool? Inverse the requirement: are you going to set all the values in the datasource to true?

Improve this answer
1
  • 2
    this post is rather hard to read (wall of text). Would you mind editing it into a better shape?
    – gnat
    Mar 22, 2018 at 6:06

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