{"id":196495,"date":"2025-07-07T09:10:26","date_gmt":"2025-07-07T08:10:26","guid":{"rendered":"https:\/\/liora.io\/en\/?p=196495"},"modified":"2026-08-09T19:53:24","modified_gmt":"2026-08-09T18:53:24","slug":"handling-missing-values-in-sql","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/handling-missing-values-in-sql","title":{"rendered":"Handling Missing Values in SQL: A Practical Guide"},"content":{"rendered":"\n

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.<\/strong><\/p>\n\n\n

? Step 1: Identify Missing Values<\/h2>\n\n\n

Before you handle missing values, you must first understand how they are represented in your dataset. \u201cMissing\u201d does not always mean NULL , it can take many forms.<\/p>\n\n\n

Here are the most common types and how to detect them:<\/p>\n\n\n

\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n
Type<\/th>\nMeaning<\/th>\nDetection in SQL<\/th>\n<\/tr>\n<\/thead>\n
NULL<\/td>\nOfficial SQL representation of a missing value<\/td>\nWHERE column IS NULL<\/td>\n<\/tr>\n
Empty String<\/td>\nValue is an empty pair of quotes (”)<\/td>\nWHERE column = ”<\/td>\n<\/tr>\n
Blank Space<\/td>\nUser input contains only spaces<\/td>\nWHERE TRIM(column) = ”<\/td>\n<\/tr>\n
Zero (0)<\/td>\nMay represent missing in some numeric fields<\/td>\nWHERE column = 0<\/td>\n<\/tr>\n
Textual \u201cmissing\u201d<\/td>\nNon-standard representations like ‘N\/A’, ‘unknown’, etc.<\/td>\nWHERE LOWER(column) IN (‘n\/a’, ‘unknown’, ‘missing’, ‘null’, ‘-‘)<\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/figure>\n\n\n

? Tip: Standardize the representations when possible, especially before ingestion if you control upstream data.<\/em><\/p>\n\n\n

Learn SQL for Data Science<\/a><\/div><\/div>\n\n\n

? Step 2: Manage Missing Values<\/h2>\n\n\n

Imagine you are working with a customer table like this:<\/p>\n\n\n

\n\n\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n
customer_id<\/th>\nfirst_name<\/th>\nlast_name<\/th>\nemail<\/th>\nemail_verified<\/th>\nphone_number<\/th>\n<\/tr>\n<\/thead>\n
101<\/td>\nJohn<\/td>\nDoe<\/td>\njohn@email.com<\/td>\nY<\/td>\n0912…<\/td>\n<\/tr>\n
102<\/td>\nJane<\/td>\nSmith<\/td>\nNULL<\/td>\nNULL<\/td>\n0933…<\/td>\n<\/tr>\n
103<\/td>\nMike<\/td>\nLee<\/td>\n”<\/td>\nN<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/figure>\n\n\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.<\/p>\n\n\n

\u2705 Option 1: Exclude Missing or Unverified Emails<\/h3>\n\n\n

If you only want to send to users with a verified email (Y), you can write:<\/p>\n\n\n

SELECT *\n\nFROM customers\n\nWHERE LOWER(email_verified) = 'y'\n\n\u00a0\u00a0AND email IS NOT NULL\n\n\u00a0\u00a0AND TRIM(email) <> '';<\/code><\/pre>\n\n\n\n
? Option 2: Use COALESCE to Substitute Missing Info<\/h3>\n\n\n
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’.<\/p>\n\n\n
You can implement this logic using a combination of CASE WHEN and COALESCE:<\/p>\n\n\n
SELECT customer_id, email\n\nFROM (\n\n\u00a0\u00a0SELECT customer_id,\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0email,\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CASE\u00a0\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN LOWER(email_verified) = 'y' THEN 'Y'\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN COALESCE(TRIM(phone_number), '') <> '' THEN 'Y'\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ELSE 'N'\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END AS email_verified_updated\n\n\u00a0\u00a0FROM customers\n\n) AS t\n\nWHERE email_verified_updated = 'Y'\n\n\u00a0\u00a0AND COALESCE(TRIM(email), '') <> '';<\/code><\/pre>\n\n\n
\u26a0\ufe0f Important Considerations<\/h2>\n\n\n
    \n
  1. Know your data: Missing values can appear in different formats. Explore your data before applying filters or replacements.<\/li>\n
  2. Do not blindly drop rows: Excluding missing data without evaluating its impact can lead to biased results or missed opportunities (e.g., important customers).<\/li>\n
  3. Communicate with stakeholders: Align on what counts as \u201cmissing\u201d and push for upstream data validation to avoid recurring issues.<\/li>\n
  4. Document your rules: Your logic for handling missing values should be transparent and reproducible.<\/li>\n<\/ol>\n\n\n
    \u2705 Conclusion<\/h2>\n\n\n
    Handling missing values in SQL<\/a> is not just about cleaning data ,  it is about making smart, context-aware decisions that preserve insight quality<\/strong> 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.<\/p>\n\n\n
    Remember:<\/p>\n\n\n