Identify Missing

We wish to identify which values of a column are missing.

This section is complemented by Inspecting Missing Values where we cover: Checking whether any missing values exist, counting missing values, and extracting rows with missing values.

Column

We wish to know which values of a column are missing.

In this example, we wish to create a new column col_1_is_na that has a value of TRUE where the corresponding value of the column col_1 is missing.

SELECT *,
       col_1 IS NULL AS col_1_is_na
FROM table_1

Here is how this works:

The standard solution for identifying missing values is the IS NULL expression. It returns TRUE if the value of col_1 is missing and FALSE otherwise.

Incomplete Row

We wish to determine which rows of a table have a missing value for any of a selected set of columns.

SELECT *,
       col_2 IS NULL OR col_3 IS NULL  AS is_incomplete
FROM table_1;

Here is how this works:

  • The standard solution for identifying missing values is the IS NULL expression. It returns TRUE if the value of col_1 is missing and FALSE otherwise.
  • We use the OR to check if col_2 or col_3 has missing value. This returns TRUE for rows where neither of the selected columns has a missing value.

Extension: Any Column

We wish to determine which rows of a table have a missing value.

SELECT *,
       TO_JSON_STRING(table_1) LIKE '%:null%' AS is_incomplete
FROM table_1;

Here is how this works:

  • The TO_JSON_STRING function is called on each row in table_1, which converts the row to a JSON string representation. For example in a table with 3 columns , the row will be repressed as "{"col_1":"str value","col_2":null,"col_3": int_value}", where col_2 here has a missing value.
  • Each row is represented as a single string, and we use LIKE '%:null%' to check if it contains :null. See String Detecting.
  • We use the substring :null to avoid including string columns that contain the substring 'null'.
  • See Drop Missing for a more detailed coverage of identifying rows with missing values.
SQL
I/O