NULL in table fields better than empty fields?

i wonder if one should have开发者_Go百科 NULL in the fields with no value, or should it be empty?

what is best?

thanks

---

NULL means that no data is set, while empty string could be some valid data.

Thus, using NULL helps you to differentiate these two cases.

---

From a programming standpoint, I try to not allow null values for a few reasons. One of which is that code often has a bad reaction to unexpected NULL values. If a query filter ran faster checking null values I might consider using them but there is no evidence of this I have experienced. But I have experienced many a function which pooped out on doing some kind of comparison not testing for NULL before hand.

---

There is a certain argument that you should never allow NULL in your data, if you are using it to indicate that you don't know what the value should be or that you just don't have that data yet then use an explicit value in the field to indicate those states. Similarly for 'empty' fields. That said, I think everyone does it or has done it and may do it again. NULL has odd comparative properties which is why it's always best, if you can, to avoid it and have explicit values for missing data states.

---

Avoid NULLs in base tables whenever three valued logic is likely to come back to bite you. That's easy to say, but lengthy to explain. Three valued logic can sometimes be successfully managed, but your intuition is likely to be based on two valued logic, and can be misleading.

If you avoid NULLS in base tables, but create views or queries with outer joins, be prepared to deal with NULLS. NULLS in fields that are never used in where clauses and never used "incorrectly" with aggregates (as in sum(FIELD)) are OK.

NULL fields are always empty, but empty doesn't always imply NULL. In particular, an empty or non existent field in a form can translate into a non NULL value in a table. Autonumber fields are an example.

Oracle made a mistake way back in the 1980s by using the same representation for the VARCHAR string of length zero (the empty string), and NULL. They've been about to fix it "real soon now" for a quarter of a century. Don't hold your breath.

Don't use NULLs to convey a meaningful message. This almost always confuses your colleagues, even when they deny it.

---

Nulls are necessary amd important tools in dataase design. If you don'tknow the value at the time the record is inseerted, null is entirely appropriate and the best practice. Making an unknon into a known value such as empty string is silly. This especially true when you get away from string data into dates or numeric data. 0 is not the same as null, some arbitrary date far in the past or future is not the same as null. For that matter empty strings means there is no value, null means we don't know what the value is. This is an important distinction.

It's not hard to handle nulls, any competent programmer should be able to do so.