Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
17  
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
7  
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
4  
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
2  
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 Woodcock Feb 6 '12 at 22:41
show 3 more comments

3 Answers

up vote 38 down vote accepted

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

share|improve this answer
3  
Succint explanation on why NULL is the value representing the absence of value. –  maple_shaft Feb 6 '12 at 20:08
1  
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
    
Comparisons that need the nulled value return null. MySQL and PostgreSQL (the only systems i have access to test at the moment) both return False for select false and null, and True for select null or true, and even return true for select false and null or true. The truth tables for AND and OR make the null side unnecessary to determine the whole expression's value. If it weren't so, you couldn't say x = 0 or x is null as a condition. –  cHao Feb 6 '12 at 22:18
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  
@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 23 hours ago
show 5 more comments

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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
2  
@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
add comment

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.