SQL NULLs: Facts that might Shock You

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.

Fact 1

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.

Example:

Select 10 * NULL
Select NULL/0 
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…