When storing missing data, should I store them as NULL?


#1

Question

When storing missing data, should I store them as NULL?

Answer

It can depend entirely on how you need the data to be stored and utilized.

Let’s say that you have a table of employee information, which included their address. Say that we wanted to check all rows of this table and find where any addresses are missing. If we stored the addresses as TEXT values, we might choose to store all the missing values as either '' or as NULL.

If we stored the missing address values as an empty string '' then these values are not NULL. Empty strings are seen as a string of length 0. So, if we ran a query using

WHERE address IS NULL

it would not give us the rows with missing address values. We would have to check using

WHERE address = ''

With a table containing many different data types, it may be helpful and more convenient to store any missing values in general as just NULL so that we can utilize the IS NULL and IS NOT NULL operators.