HANDLING MISSING VALUES/NULLS

In the world of data, deciding how to handle missing values is a common task. Is the absence of a value meaningful or meaningless? Will the absence of a value negatively affect our ability to make an accurate calculation resulting in a value that isn’t misleading? Do the consumers of our dashboards and/or reports understand the concept of NULL and if they do, is it desirable to populate a value instead of showing nothing or NULL? These are only a handful of the possible questions you may need to answer regarding NULLs or missing values. Let’s quickly look at a widely available function across relational database management system products and then dive into a few examples.

Step forward, COALESCE. The COALESCE function accepts two or more arguments and returns the first non-null argument. Easy right? Now for the examples. The schemas, corresponding tables, and data used in the examples can be found at livesql.oracle.com.

At times, it may be ideal to populate NULLs or missing values with a value that provides meaning to the consumers of your data. In the following two examples, missing values are populated with the value 999. In the first example, the value is populated with 999 to indicate the employee isn’t managed by another employee. In the second example, orders without an associated sales representative are populated with the value 999.

-- Replace missing manager ID values with 999.
SELECT
    hr.employees.employee_id,
    COALESCE(hr.employees.manager_id, 999) AS manager_id
FROM
    hr.employees;

-- Replace missing sales representative ID values with 999.
SELECT
    COALESCE(oe.orders.sales_rep_id, 999) AS sales_rep_id,
    CASE
        WHEN oe.orders.sales_rep_id IS NULL
            THEN 'Online'
        ELSE 'In-store'
    END AS order_type,
    SUM(oe.orders.order_total) AS order_total
FROM
    oe.orders
GROUP BY
    sales_rep_id,
    CASE
        WHEN oe.orders.sales_rep_id IS NULL
            THEN 'Online'
        ELSE 'In-store'
    END
ORDER BY
    sales_rep_id DESC;

The two examples above are relatively straightforward. The scenario and use of COALESCE simply replaced the missing values with a hard-coded default value. In the next example, product price values will be imputed based on the average price of those products in the same category. The strategy below allows us to step away from hard-coding missing values. Instead, the missing values will reflect the average price at run-time.

-- Solution utilizing a CTE and a correlated subquery in the SELECT clause.
WITH impute AS (
    SELECT
    	product.category_id,
    	AVG(product.list_price) AS average_price
    FROM
    	oe.product_information product
    GROUP BY
    	product.category_id
)
SELECT
    product.product_id,
    product.category_id,
    COALESCE(product.list_price,
        (
            SELECT
                impute.average_price
            FROM
                impute
            WHERE
                impute.category_id = product.category_id
        )
    ) AS list_price
FROM
    oe.product_information product;

-- Solution utilizing a CTE and an INNER join.
WITH impute AS (
    SELECT
    	product.category_id,
    	AVG(product.list_price) AS average_price
    FROM
    	oe.product_information product
    GROUP BY
    	product.category_id
)
SELECT
    product.product_id,
    product.category_id,
    COALESCE(product.list_price, impute.average_price) AS list_price
FROM
    oe.product_information product
    INNER JOIN
    impute
        ON product.category_id = impute.category_id;