"some arbitrary token value to represent the fact that it is unknown" this is known as a sentinel value — Alexander Oct 7 '17 at 6:23

But what prevents you from creating separate table birth_date where you store birth dates? If birth date is unknown then just don't insert date of birth into birth_date. Nulls are disaster. — Eldar Agalarov Nov 16 '17 at 23:07

@EldarAgalarov That sounds like Trump reasoning (“disaster” why? How? For whom? Your opinion that something is a "disaster" doesn't make it so). Anyway birth date is just one example. If you have personnel or members or customers that have 15 potentially nullable columns, are you going to create 15 secondary tables? What if you have 50? What if your DW fact table has 500? Maintenance to keep big bad scary NULLs out of your database becomes 10x as bad as any “disaster” you’re afraid of... — Aaron Bertrand Nov 17 '17 at 0:43

@AaronBertrand if your table has 15 potentially nullable columns, it smells really bad ^^ Not that a huge number of column is inherently bad, but it may indicate a bad design OR required denormalization. But it will raise questions. — programaths Jan 7 '18 at 11:27

@EldarAgalarov is correct and nulls are a disaster for the same reason that allowing division by zero is a disaster: it breaks logic. The simplest reason why three-valued logic is a disaster in database logic is that any individual record must either be returned or not returned by any individual query. The DBMS cannot leave it undecided whether to include a record or not. Thus, NULL must always be coerced to either true or false and it is this that causes problems. — Wildcard Sep 17 '18 at 21:06

"NULL makes a lot more sense than picking some arbitrary token value to represent the fact that it is unknown" -- this is a strawman. I have read no critic of NULLs who actually advocates arbitrary token values to represent "unknown" information. Rather, unknowns are handled through proper schema design, as covered in How to Handle Missing Information Without Nulls. — Wildcard Sep 18 '18 at 3:52

@Wildcard So you’ve never seen people store 1900-01-01 to avoid having a NULL date/time value? Ok then. Also, NULL = unknown and unknown = false. I'm not sure what problems this might cause other than people aren't born knowing that (like they aren't born knowing a lot of things inherent in a complex RDBMS). Again, waving hands and saying "Problem! Disaster!" doesn't make it so. — Aaron Bertrand Sep 18 '18 at 13:02

@AaronBertrand your mischaracterization of NULL and false is illustrative of why people shouldn't use NULL. NULL != false. Those types of subtle differences lead to problems. — rich remer Jun 8 '19 at 15:19

@richremer I think you're confusing my "mischaracterization" with how SQL Server actually works. NULL is unknown and unknown will evaluate to false. WHERE id IN (SELECT NULL) is false, there's just no other way to express "unknown" when determining when to include a row or not. You can't "I don't know," you either need to return it, or not. Saying someone shouldn't use something just because other people have problems understanding how it works is downright silly IMHO. If you want to avoid NULLs in your systems for this reason, have at it, but "avoid it" isn't a recipe for everyone else. — Aaron Bertrand Jun 8 '19 at 16:12

@AaronBertrand frankly i'm surprised by your answer. NULL is a meaningless and ambiguous value. it is avoided in DDD. — Alex Gordon Nov 10 '20 at 21:24

@AlexGordon Meaningless is the point. "We don't know the value." How is it ambiguous? "I don't know what this value is." is not ambiguous. Is it ambiguous because you might have different reasons for not knowing the value at this time? If that's important, couldn't you record that as a separate fact instead of using some token, magic date like 1900-01-01? I don't have a doctorate in DDD but I know I'm not going to prevent a patient from being admitted because we can't add a row to the table because their birthdate is unknown and a constraint prevents NULL. — Aaron Bertrand Nov 10 '20 at 21:31

@AaronBertrand by allowing nulls in the BirthDate field you are introducing a weird value for birthdate. NULL is not a birthdate. you are affecting all of the layers of your application, and you are hiding your business logic rule "a patient can be admitted without a birthdate" — Alex Gordon Nov 10 '20 at 21:37

@AlexGordon It's not a weird value. NULL is not a value. It is explicitly and precisely the lack of a value. If you don't know the birthdate, what value would be "less weird"? And as the data steward, the application layer is not really my problem. If we don't know the birthdate and they still want to display something, they can. If they choose an empty string or a question mark or 1753-12-31 or a poop emoji, cool. None of those "less weird" values have to be stored in the database to solve the application layer problem. — Aaron Bertrand Nov 10 '20 at 22:04

@AaronBertrand ah yeah sorry I misread a bit — James Nov 24 '21 at 13:27