19

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.

  • 25
    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 '12 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 '12 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 '12 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 '12 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 '12 at 22:41
50

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

  • 3
    Succint explanation on why NULL is the value representing the absence of value. – maple_shaft Feb 6 '12 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 '12 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 '12 at 23:42
  • 1
    @S.Lott, but doesn't storing null instead of false save some space?? – azerafati May 18 '14 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 '14 at 15:13
15

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.

5

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.

  • 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 '12 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 '12 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?

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

protected by gnat Mar 22 '18 at 6:04

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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