Factsheet
What is NULL in SQL? - Stack Overflow
Where do NULL values come from in datasets and how to handle them?
rdbms - Null value in Database - Stack Overflow
Why in firebase rules exists() function gives me a null value error? any help is greatly appreciated.
Videos
In simple worlds you can say that Null is not a data value, but a marker for an unknown value.
So any mathematical operations performed on NULL will result in NULL. For example,
10 + NULL = NULL
Similarly if you do string concatenation with string you get:-
'String ' || NULL || 'Concatenation' -- Result is NULL
So you can say that Null means either "not applicable" or "don't know": it is not the same as zero (0) or any other default value, but more importantly, null is treated quite differently from other values in SQL, because it literally has no value.
An example to explain what it means when we say that NULL means UNKNOWN VALUE:
StudentName TestResult
X 78
A 89
B 67
C NULL
So you can see that the student C got NULL marks in the test. So what does that mean?
Now one can say that the student does not sit in the test or it may be that the student's data is not avaialable. But it definitely does not mean that the student got 0(as if you assign 0 to the student then it would mean that the student appeared in the test and got zero) marks in the test. All we can say that the data for the student is
UNKNOWN or NULL
A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.
NULL values are treated differently from other values.
NULL is used as a placeholder for unknown or inapplicable values. Read more about this here.
My understanding is that NULL represents true absence of value or a total unknown value. This is not the same as empty which is a known value, or a string of zero length. I've worked with banking data and often see lots of NULL values in various fields but if NULL represents UNKNOWN does that mean something simply went wrong/error in the system or is it a legitimate value? Because otherwise I'd think putting empty there makes more sense.
Not really sure how to treat NULL values in these datasets, should I simply ignore them? What if I'm trying to transform the data (or preform joins) on these rows wouldn't NULL values throw all the calculations off?
How should I think about and handle NULL values as they come into my codebase?
Thanks
It's all about the context in which it's used. A null means there is no value but the reason for this will depend on the domain in which it is being used. In many cases the items you've listed are all valid uses of a null.
It can mean any of those things (and it is not always obvious which), which is one argument against using nulls at all.
See: http://en.wikipedia.org/wiki/Null_(SQL)#Controversy