Handling SQL Nulls effectively and writing bug free code is something I am yet to master, even after writing SQL for over 8 years. In this blog post, I will expose some of the facts about Nulls that I didn’t even know and share some of the common coding mistakes I have made while handling nulls so far in my career. If you prefer watching my Youtube video on this topic, please check it out here.
What is NULL? Null in SQL literally means “Unknown” or “Missing Information”. Please note that it does not mean integer “0” nor empty string.
In Relational Database, NULL makes logical evaluations to be 3 valued. This means an expression in SQL can result to True, False or Unknown.
NULL when combined with any literal value like integer, string, date values will result to a NULL. Think of it like this, if I add 1 to an unknown number, the result will still be unknown.
Select 10 * NULL
Select 10 + Null
select 'Abc' || Null
All the above Select queries result to a Null.
Note that dividing NULL by 0 returns NULL instead of an exception.
In addition when you concatenate a field with another field that might have NULLs, or the field is Nullable, expect that the result might be NULL now or in the FUTURE (if a null record is inserted in the nullable column)!
NULLs in Logical Evaluations will return a Null value
select NULL = 1
select NULL < 10
select NULL <> 'Hello'
select 1 != Null
select null = null
All the above statements evaluate to NULLs. Think about it, compare an UKNOWN value to either another Unknown value or a known value the result will always be unknown.
Fact 3: Null when it used with a Logical Operator can either return True, False or Unknown
Select NULL and True
Returns a NULL, because NULL in this case represent either (True or False). Hence the final result will be unknown or NULL
Select NULL and False