Handling Missing Values in SQL

Context

Missing values are common in analytics tables and operational datasets. Interviewers ask this question to see whether you can distinguish between simply replacing NULLs and making a sound data-cleaning decision.

Core question

Explain how you would handle missing values in a SQL dataset. Your answer should cover:

1. How to identify missing values and understand why they are missing
2. When to leave NULLs as-is versus filling them with defaults or derived values
3. Common SQL techniques for handling missing data in numeric, categorical, and date fields
4. Risks of introducing bias or incorrect business logic when imputing values

Scope guidance

The interviewer is usually looking for a practical, SQL-oriented explanation rather than advanced statistical imputation. Focus on data quality checks, business context, and common PostgreSQL techniques such as COALESCE, CASE WHEN, filtering, and aggregation-based replacement strategies.

Identify missing values first

Before changing data, determine which columns contain NULLs and how often they occur. You should also check whether missingness is expected, accidental, or meaningful from a business perspective.

SELECT COUNT(*) AS total_rows,
       COUNT(*) FILTER (WHERE email IS NULL) AS missing_email,
       COUNT(*) FILTER (WHERE salary IS NULL) AS missing_salary
FROM employees;

Use COALESCE for simple defaults

COALESCE returns the first non-NULL value in a list and is the most common SQL tool for replacing missing values in query output. It is useful for reporting, but default values should reflect business meaning rather than convenience.

SELECT employee_id,
       COALESCE(department, 'Unknown') AS department,
       COALESCE(bonus, 0) AS bonus
FROM employees;

Conditional handling with CASE

CASE WHEN is useful when replacement logic depends on other fields or business rules. This is better than applying one blanket default to all missing values.

SELECT employee_id,
       CASE
         WHEN termination_date IS NULL THEN 'Active'
         ELSE 'Inactive'
       END AS employee_status
FROM employees;

Aggregation-based imputation

For some easy interview scenarios, missing numeric values can be filled with a summary statistic such as an average. This should be used carefully because it can hide data quality issues and distort downstream analysis.

SELECT employee_id,
       COALESCE(salary, AVG(salary) OVER ()) AS filled_salary
FROM employees;

Sometimes NULL should remain NULL

A missing value is not always an error. In many cases, preserving NULL is the correct choice because it distinguishes unknown data from a real zero, empty string, or placeholder.

SELECT employee_id, last_promotion_date
FROM employees
WHERE last_promotion_date IS NULL;