{"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-02-06T07:43:49","modified_gmt":"2026-02-06T06:43:49","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":"<strong>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>\n\n<style><br \/>\n.elementor-heading-title{padding:0;margin:0;line-height:1}.elementor-widget-heading .elementor-heading-title[class*=elementor-size-]>a{color:inherit;font-size:inherit;line-height:inherit}.elementor-widget-heading .elementor-heading-title.elementor-size-small{font-size:15px}.elementor-widget-heading .elementor-heading-title.elementor-size-medium{font-size:19px}.elementor-widget-heading .elementor-heading-title.elementor-size-large{font-size:29px}.elementor-widget-heading .elementor-heading-title.elementor-size-xl{font-size:39px}.elementor-widget-heading .elementor-heading-title.elementor-size-xxl{font-size:59px}<\/style>\n<h2>? Step 1: Identify Missing Values<\/h2>\nBefore you handle missing values, you must first understand how they are represented in your dataset. \u201cMissing\u201d does not always mean NULL \u2014 it can take many forms.\n\nHere are the most common types and how to detect them:\n\n<style type=\"text\/css\">\n.tg  {border-collapse:collapse;border-spacing:0;}<br \/>\n.tg td{border-color:black;border-style:solid;border-width:1px;font-family:Rubik, sans-serif;font-size:16px;<br \/>\n  overflow:hidden;padding:10px 5px;word-break:normal;}<br \/>\n.tg th{border-color:black;border-style:solid;border-width:1px;font-family:Rubik, sans-serif;font-size:16px;<br \/>\n  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}<br \/>\n.tg .tg-5jfb{background-color:#CFE2F3;text-align:left;vertical-align:top}<br \/>\n.tg .tg-j6nv{background-color:#073763;color:#FFF;font-weight:bold;text-align:center;vertical-align:top}<br \/>\n<\/style>\n<table style=\"undefined;table-layout: fixed; width: 825px\">\n<colgroup>\n<col style=\"width: 125px\">\n<col style=\"width: 350px\">\n<col style=\"width: 350px\">\n<\/colgroup>\n<thead>\n<tr>\n<th>Type<\/th>\n<th>Meaning<\/th>\n<th>Detection in SQL<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>NULL<\/td>\n<td>Official SQL representation of a missing value<\/td>\n<td>WHERE column IS NULL<\/td>\n<\/tr>\n<tr>\n<td>Empty String<\/td>\n<td>Value is an empty pair of quotes (&#8221;)<\/td>\n<td>WHERE column = &#8221;<\/td>\n<\/tr>\n<tr>\n<td>Blank Space<\/td>\n<td>User input contains only spaces<\/td>\n<td>WHERE TRIM(column) = &#8221;<\/td>\n<\/tr>\n<tr>\n<td>Zero (0)<\/td>\n<td>May represent missing in some numeric fields<\/td>\n<td>WHERE column = 0<\/td>\n<\/tr>\n<tr>\n<td>Textual \u201cmissing\u201d<\/td>\n<td>Non-standard representations like &#8216;N\/A&#8217;, &#8216;unknown&#8217;, etc.<\/td>\n<td>WHERE LOWER(column) IN (&#8216;n\/a&#8217;, &#8216;unknown&#8217;, &#8216;missing&#8217;, &#8216;null&#8217;, &#8216;-&#8216;)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n? <em>Tip: Standardize the representations when possible, especially before ingestion if you control upstream data.<\/em>\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center\"><div class=\"wp-block-button \"><a class=\"wp-block-button__link wp-element-button \" href=\"\/en\/courses\/data-ai\/data-analyst\">Learn SQL for Data Science<\/a><\/div><\/div>\n\n<h2>? Step 2: Manage Missing Values<\/h2>\nImagine you are working with a customer table like this:\n\n<style type=\"text\/css\">\n.tg  {border-collapse:collapse;border-spacing:0;}<br \/>\n.tg td{border-color:black;border-style:solid;border-width:1px;font-family:Rubik, sans-serif;font-size:16px;<br \/>\n  overflow:hidden;padding:10px 5px;word-break:normal;}<br \/>\n.tg th{border-color:black;border-style:solid;border-width:1px;font-family:rubik, sans-serif;font-size:16px;<br \/>\n  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}<br \/>\n.tg .tg-5jfb{background-color:#CFE2F3;text-align:left;vertical-align:top}<br \/>\n.tg .tg-j6nv{background-color:#073763;color:#FFF;font-weight:bold;text-align:center;vertical-align:top}<br \/>\n<\/style>\n<table style=\"undefined;table-layout: fixed; width: 800px\">\n<colgroup>\n<col style=\"width: 125px\">\n<col style=\"width: 115px\">\n<col style=\"width: 115px\">\n<col style=\"width: 150px\">\n<col style=\"width: 150px\">\n<col style=\"width: 145px\">\n<\/colgroup>\n<thead>\n<tr>\n<th>customer_id<\/th>\n<th>first_name<\/th>\n<th>last_name<\/th>\n<th>email<\/th>\n<th>email_verified<\/th>\n<th>phone_number<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>101<\/td>\n<td>John<\/td>\n<td>Doe<\/td>\n<td>john@email.com<\/td>\n<td>Y<\/td>\n<td>0912&#8230;<\/td>\n<\/tr>\n<tr>\n<td>102<\/td>\n<td>Jane<\/td>\n<td>Smith<\/td>\n<td>NULL<\/td>\n<td>NULL<\/td>\n<td>0933&#8230;<\/td>\n<\/tr>\n<tr>\n<td>103<\/td>\n<td>Mike<\/td>\n<td>Lee<\/td>\n<td>&#8221;<\/td>\n<td>N<\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nYou are planning an email campaign where each email costs money, so you want to avoid sending to people with unverified or missing email addresses.\n<h3>\u2705 Option 1: Exclude Missing or Unverified Emails<\/h3>\nIf you only want to send to users with a verified email (Y), you can write:\n<pre>SELECT *\n\nFROM customers\n\nWHERE LOWER(email_verified) = 'y'\n\n&nbsp;&nbsp;AND email IS NOT NULL\n\n&nbsp;&nbsp;AND TRIM(email) &lt;&gt; '';<\/pre>\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center\"><div class=\"wp-block-button \"><a class=\"wp-block-button__link wp-element-button \" href=\"\/en\/courses\/data-ai\/data-analyst\">Learn to manage missing values in SQL<\/a><\/div><\/div>\n\n<h3>? Option 2: Use COALESCE to Substitute Missing Info<\/h3>\nNormally, you would only trust customers with email_verified = &#8216;Y&#8217;. 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 &#8216;N&#8217;.\n\nYou can implement this logic using a combination of CASE WHEN and COALESCE:\n<pre>SELECT customer_id, email\n\nFROM (\n\n&nbsp;&nbsp;SELECT customer_id,\n\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;email,\n\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CASE&nbsp;\n\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN LOWER(email_verified) = 'y' THEN 'Y'\n\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN COALESCE(TRIM(phone_number), '') &lt;&gt; '' THEN 'Y'\n\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE 'N'\n\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END AS email_verified_updated\n\n&nbsp;&nbsp;FROM customers\n\n) AS t\n\nWHERE email_verified_updated = 'Y'\n\n&nbsp;&nbsp;AND COALESCE(TRIM(email), '') &lt;&gt; '';<\/pre>\n<h2>\u26a0\ufe0f Important Considerations<\/h2>\n<ol>\n \t<li style=\"font-weight: 400;\" aria-level=\"1\">Know your data: Missing values can appear in different formats. Explore your data before applying filters or replacements.<\/li>\n \t<li style=\"font-weight: 400;\" aria-level=\"1\">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 \t<li style=\"font-weight: 400;\" aria-level=\"1\">Communicate with stakeholders: Align on what counts as \u201cmissing\u201d and push for upstream data validation to avoid recurring issues.<\/li>\n \t<li style=\"font-weight: 400;\" aria-level=\"1\">Document your rules: Your logic for handling missing values should be transparent and reproducible.<\/li>\n<\/ol>\n<h2>\u2705 Conclusion<\/h2>\nHandling missing values in <a href=\"https:\/\/liora.io\/en\/sql-tutorial-top-5-most-useful-methods\">SQL<\/a> is not just about cleaning data \u2014 it is about making smart, context-aware decisions that <strong>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.\n\nRemember:\n<ul>\n \t<li style=\"font-weight: 400;\" aria-level=\"1\"><strong>Explore first<\/strong> \u2014 understand how missing values are represented in your dataset.<\/li>\n \t<li style=\"font-weight: 400;\" aria-level=\"1\"><strong>Act selectively<\/strong> \u2014 not all missing values should be removed; some may be handled through logic.<\/li>\n \t<li><strong>Think practically<\/strong> \u2014 missing values often reflect real-world constraints, so align with stakeholders on how to interpret and treat them.<\/li>\n<\/ul>\nBy thoughtfully identifying and managing missing values, you will build analyses that are not only cleaner, but also more trustworthy and actionable.\n\n<a href=\"\/en\/courses\/data-ai\/data-analyst\">\nLearn to handle missing values in SQL\n<\/a>","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":99,"featured_media":196496,"comment_status":"open","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"_acf_changed":false,"editor_notices":[],"footnotes":""},"categories":[2433],"class_list":["post-196495","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-ai"],"acf":[],"_links":{"self":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/196495","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/users\/99"}],"replies":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/comments?post=196495"}],"version-history":[{"count":5,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/196495\/revisions"}],"predecessor-version":[{"id":205537,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/196495\/revisions\/205537"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media\/196496"}],"wp:attachment":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media?parent=196495"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/categories?post=196495"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}