What are the NULL fields for the database for?

Question

Can someone explain the use of NULL in database tables? What is it used for and how is it useful?

Answer 1

It is useful to mark "unknown" or missing values.

For example, if you store people, and one of the fields is their birthday, you can allow it to be NULL to mean "We do not know when he was born."

This is different from non-empty fields, where for the same type you will need to designate a "magic value" that has the same value.

For example, what date should you keep in this birth field to mean “we don’t know when he was born”? Should 1970-01-01 be a magical value? What if we suddenly need to save a person born that day?

Now, saying that NULL is one of the hardest problems to handle in design and database engines, as it is a property of spreading values.

For example, that is the result of the following:

 SELECT * FROM people WHERE birthday > #2000-01-01# SELECT * FROM people WHERE NOT (birthday > #2000-01-01#) 

(note that the syntax of the above date may not be legal in any database engine, do not focus on it)

If you have a lot of people with an "unknown" birthday, i.e. NULL , they will not be displayed in any of the results.

Since in the above two queries the first says “all people with criteria X”, and in the second “all people without criteria X”, you think that together the results of these two queries will produce all the people in the database.

However, the queries actually say "all people with a known and specific X criterion."

This is similar to the question: "Am I over 40?" on which the other person says "I do not know." If you then ask, “Am I under the age of 41?”, The answer will be the same, he still does not know.

In addition, any math or comparison will create NULL .

For example, that is:

 SELECT 10 + NULL AS X 

the result is NULL because "10 + unknown" is still unknown.

Answer 2

From Wikipedia :

Introduced by the creator of the relational database model, EF Codd, SQL Null serves to fulfill the requirement that all true relational database management systems (RDBMS) support the representation of "missing information and not applicable information"

Answer 3

This is something like a column filled with "I don't know," or "doesn't matter." For example, in the customer table, if they do not fill in their phone number, what do you put in the phone number field?

Answer 4

Imagine the absence of any meaning.

NULL can be used to store additional data in a table. Say you have a table. Not all cars have chests ... so their TrunkSpace column may be NULL

Answer 5

I like the answer of JRL / Wikipedia. It is sometimes advisable to populate a NULL column if you do not want the default to match the actual value. I mean, there are times when an empty string can be used instead of NULL when you are working with varchar. But what about a bit or a date type? If a bit column is used, then a default value of 0 or 1 may not be sufficient. And what should be the default date for a date type?

There is another reason to use NULLS on some systems. SQL Server 2008 introduced the concept of sparse columns , which optimized storage for NULL values. They are best used when you have a column that is likely to be mostly empty (or basically NULL, to be exact).

Answer 6

"What is it used for and how useful is it?"

It is used to “solve” (and please note the quotation marks) the problem of help with missing information.

It is useful for creating crap loads of both software development problems and software implementation problems (the most egregious of them can be summarized as "(x == y) ==! (X == y)", or in other words: It is useful for creating job security for a crappy load of programmers who claim that zeros are inevitable.

Answer 7

Please check this question .

“Null” basically means “It doesn't matter,” but it can also mean “what value is unknown,” thereby making its intepretation sometimes ambiguous. Think of a people table showing your birth date and n # driver's license. If the date of birth does not matter, it means that the date is unknown (everyone was born the other day ...). Now, if the driver’s license field does not have any value, does this mean that the person does not have a driver’s license, or does it mean that his n # is unknown?

Answer 8

It simply means “missing values” or “values ​​that are unknown” during insertion into the database for specific records.