Missing values are one of the most common data quality issues. If not handled properly, they can bias your reports, skew insights, or hurt forecasting accuracy. In SQL, there are several ways to detect, interpret, and manage missing values depending on the context.
🔎 Step 1: Identify Missing Values
Before you handle missing values, you must first understand how they are represented in your dataset. “Missing” does not always mean NULL — it can take many forms.
Here are the most common types and how to detect them:
| Type | Meaning | Detection in SQL |
|---|---|---|
| NULL | Official SQL representation of a missing value | WHERE column IS NULL |
| Empty String | Value is an empty pair of quotes ('') | WHERE column = '' |
| Blank Space | User input contains only spaces | WHERE TRIM(column) = '' |
| Zero (0) | May represent missing in some numeric fields | WHERE column = 0 |
| Textual “missing” | Non-standard representations like 'N/A', 'unknown', etc. | WHERE LOWER(column) IN ('n/a', 'unknown', 'missing', 'null', '-') |
💡 Tip: Standardize the representations when possible, especially before ingestion if you control upstream data.
🛠 Step 2: Manage Missing Values
Imagine you are working with a customer table like this:
| customer_id | first_name | last_name | email_verified | phone_number | |
|---|---|---|---|---|---|
| 101 | John | Doe | john@email.com | Y | 0912... |
| 102 | Jane | Smith | NULL | NULL | 0933... |
| 103 | Mike | Lee | '' | N |
You are planning an email campaign where each email costs money, so you want to avoid sending to people with unverified or missing email addresses.
✅ Option 1: Exclude Missing or Unverified Emails
If you only want to send to users with a verified email (Y), you can write:
SELECT *
FROM customers
WHERE LOWER(email_verified) = 'y'
AND email IS NOT NULL
AND TRIM(email) <> '';
🔄 Option 2: Use COALESCE to Substitute Missing Info
Normally, you would only trust customers with email_verified = ‘Y’. But based on internal logic, you decide to treat email addresses as valid if the customer has a phone number, even if the email_verified column is missing or marked as ‘N’.
You can implement this logic using a combination of CASE WHEN and COALESCE:
SELECT customer_id, email
FROM (
SELECT customer_id,
email,
CASE
WHEN LOWER(email_verified) = 'y' THEN 'Y'
WHEN COALESCE(TRIM(phone_number), '') <> '' THEN 'Y'
ELSE 'N'
END AS email_verified_updated
FROM customers
) AS t
WHERE email_verified_updated = 'Y'
AND COALESCE(TRIM(email), '') <> '';
⚠️ Important Considerations
- Know your data: Missing values can appear in different formats. Explore your data before applying filters or replacements.
- Do not blindly drop rows: Excluding missing data without evaluating its impact can lead to biased results or missed opportunities (e.g., important customers).
- Communicate with stakeholders: Align on what counts as “missing” and push for upstream data validation to avoid recurring issues.
- Document your rules: Your logic for handling missing values should be transparent and reproducible.
✅ Conclusion
Handling missing values in SQL is not just about cleaning data — it is about making smart, context-aware decisions that preserve insight quality and business impact. Whether you are identifying true NULLs, interpreting unconventional missing markers, or applying business logic to fill gaps, the key is to approach the task intentionally.
Remember:
- Explore first — understand how missing values are represented in your dataset.
- Act selectively — not all missing values should be removed; some may be handled through logic.
- Think practically — missing values often reflect real-world constraints, so align with stakeholders on how to interpret and treat them.
By thoughtfully identifying and managing missing values, you will build analyses that are not only cleaner, but also more trustworthy and actionable.
