Null Values
NULL is a special value that represents either unknown or inapplicable data.
- means that there is no value in a field
- not the same as zero or blank
- might appear to:
- be blank
- contain the word “NULL”
- return an N/A
- can still give valuable insight
- leads to investigating issues
- some columns should never contain a
NULLNOT NULLconstraint prevents a column from having aNULLvalue- follows after the column name and data type in a
CREATE TABLEstatement
- follows after the column name and data type in a
Causes of Null Values
- Value is Not Applicable to the Field
- Null values can occur when the value isn’t applicable to that field
- Data Set Does Not Yet Have the Information
- might encounter null values when the data set does not have all the information it needs at that time
- data is unavailable and has not yet been created
- Data Set Has No Match for Expected Information
- can occur when there is no match for the expected information
- Survey Data is Incomplete
- when user skips a survey question
- Data Was Excluded Due to an Incorrect Join Type
- incorrect join types can exclude data
- inner joins exclude data that don’t match in both tables
- often a default join
Filtering Null Values
- In SQL, can include
IS NULLin theWHEREstatement to get a list of null values - have to truly understand why the value is null in order to address it effectively
Troubleshooting Null or Missing Data
- When null values are not supposed to be in the data, a data analyst must address them
- one of the first things you might investigate is whether the value is null for every record in that column
- or if the values started showing up as null at a certain point
- If values were present, but then suddenly stopped,
- you might look at the metadata of that first field with a null value
- a change in the system may have caused this field to no longer capture data
- you might look at the metadata of that first field with a null value
- If you first detect a null value on your dashboard
- high probability that you’re dealing with a join issue
- look at the source and model of the dashboard, and then work your way backwards toward the source
- high probability that you’re dealing with a join issue