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 the result might be NULL now or in the FUTURE (if a null record is inserted in the nullable column)!

Fact 2

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

AND Operator:

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