I think I agree with you. What I think is even more important, though, is that the standard is set and followed. If there is no string input, across the board it has to be NULL, instead of an empty string. I can't stand expecting one or the other for the lack of data. +1, @AaronBertrand.

I have worked on software systems where someone decided to use special values instead of database NULLs and the resulting application code was a horror. There were tests for magic numbers everywhere.

Agreed, after 5+ years of DB dev (Oracle, MySQL, pg). Also, you might want to add comments to the columns if NULLs have a more specific semantic, such as "not applicable," "not yet measured," or "NaN."

So why design a table with columns where you won't always know the value? Instead, change the design: put that column in a different table, whose predicate indicates the separate truth you're trying to convey. If you know the value, create the row; if not, not. No special-case values or maybe-we-don't-have-a-value needed.

@bignose so you have an entity like Customer, and 4 or 5 attributes that you may or may not know when the row is created, you should have 4 or 5 tables for the extra attributes? And create a new table each time you add a new attribute? I think you can maintain that in a classroom but not in a production system. What is wrong with having NULL instead of an absent row? Logically they are similar but now your joins become quite tedious to write...

@Aaron, your points are one of the main reasons few people do use that design. SQL syntax for joins is not nearly as convient as it could be, unless of course you name your columns such that "normal" joins can always be accomplished via natural joins. (Of course, unusual cases may always require explicit joins.) The other main reason people don't often do this is obviously performance in the many join case.

@Aaron, the comment from Kevin mirrors my own thoughts. See my answer on this question for the “what's wrong with NULL” aspect.

@bignose you're avoiding my question. I think your answer paints NULLs as this wicked witch of the west, and most of your reasoning involves lack of education about NULL, but you ignore what to do when you have an entity with several possibly unknown attributes. So to avoid NULLs entirely, you'd advocate a design that had 15 tables that may or may not have rows, compared to a single table with 15 nullable columns? You'd have a real hard time pulling that off in any of the shops I've worked/consulted.

I haven't advocated avoiding NULLs entirely with current SQL capabilities. Rather, I point out the failings of NULLs and their undesirability, and advocate changing the RDBMS state of art to a point where we can feasibly avoid NULL.

@bignose your suggestion above was advocating putting those "truths" in separate tables. All that does is shift the "dealing with NULL" problem from a COALESCE to an OUTER JOIN - and lots more of them if we're talking about more than one attribute. I don't see anything in there about changing the way the RDBMS works. Perhaps you could elaborate and explain how that would work in this context.

I hope you're not expecting elaboration on such detailed issues in comments here; this is already pretty messy :-)

@AaronBertrand is it not faster a query against null than 0, or any other vaue?

@jcho360 are you suggesting that NULLs should be avoided because you think checking some token value is faster than checking if the value is NULL? Even if there is some miniscule difference, it is going to be so miniscule in comparison to everything else that's going on in the query that it really doesn't matter.

@AaronBertrand no, I'm asking you the other way. Use null values because they are faster to checking.

@jcho360 I think my answer is the same. :-) This is not among the criteria I would use to evaluate whether or not to allow NULLs.

@AaronBertrand haha thanks, I asked you because I wanted to be 100% about the nulls fields ;)

+1. Tired of 'NULL is bad m'ok" !

NULL has no place in set theory - I think that is where some of the objections come from. I'm not going to argue the case, I'll just state my preference is to avoid them and so far none of my databases have NULL. The majority seem to be fine using them though, so you'll have to make your own choice about what seems correct to you. I think the main thing to avoid is allowing a NULLable foreign key (IMO).

@Mark In theory, only. In practice, to avoid NULL values one would have to make a table for every field for which "unknown" is a possibility (kinda 6NF-ish), which just isn't feasible in pretty much every situation....

@MirroredFate: I think it is misleading to say it isn't feasible. In you're experience, for some reason you have found it unfeasible. My experience is the opposite: I never allow NULLs, and it leads to a nice clean DB. The only place I allow NULLs is in VIEWS or history tables (as I consider history tables de-normalised snapshots of normalised data).

@Mark I cannot help but think you must have had very limited or exceptional experience, in that case. Here is a real-world example: say I have a record in a table representing a shipment. I don't have the PO number for that shipment yet, because the part of the process that generates PO numbers hasn't happened. What do I do? Not have a PO column? Have an entire separate table for a single field value, that I have to do joins on? Define some negative number as my PO value? Or just recognize that NULL means unknown, and use NULL?

@MirrorFate: Please don't presume to judge my experience. I have software installed on mission critical sites all over the world. Yes of course an "entire" separate table. Why is that such a big deal? You make a table sound like this huge additional overhead in the database. Joins are what databases are designed to do and they do it very well. If you want a logical view of the data that has all columns, you create a view (hence the name). It might sound cumbersome but in the long run I have always found it works out cleaner. Also having several smaller tables can work out to be an ..

advantage. Sometimes you may only want to query against your PO value.. you have a nice narrow table to query or inner join against (you are guaranteed no records without a PO..). Anyway I guess your experience has been different to mine. At the end of the day as long as we both produce proper provable work, perhaps its not worth arguing over. I do definitely advocate NOT NULL though, having experience of both methodologies in my 17 years freelancing.

PS - it is also nice when you reduce it back to set theory, knowing you have a "set of PO" and a "set of shipment" .. Makes some very clean SQL queries..

I did used to do the same as you at the beginning until I worked with an old pro for a while, and once I changed and got used to the idea, it was a case of not looking back. I don't mean to "appeal to authority", but its just my experience I am sharing. Like I said, no point arguing, it never leads anywhere on the Internet, and its a fairly trivial point in the grand scheme of things :-)

Can we say: mandatory = NOT NULL(able) ### NOT mandatory = NULL(able) ?? :)