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 NULL
    • NOT NULL constraint prevents a column from having a NULL value
      • follows after the column name and data type in a CREATE TABLE statement

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 NULL in the WHERE statement 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
  • 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