{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"missing-values-honestly","title":"Missing values: NULL, empty, N/A, zero and minus one","url":"https://addaly.com/learn/data-and-sql/missing-values-honestly","position":51,"of":66,"minutes":9,"answer":"Sentinels like empty strings, N/A, zero and minus one are values a query will happily average, so convert them to NULL, keep a flag for missingness, and fill only in the feature table.","keyIdea":"Sentinels like empty strings, N/A, zero and minus one are values a query will happily average, so convert them to NULL, keep a flag for missingness, and fill only in the feature table, because every fill shrinks variance or invents a cluster."},"body":{"format":"markdown","text":"## Five spellings of \"we do not know\"\n\nThe NULL lesson covered how a database compares an unknown. This one is about what to do when a column full of unknowns arrives, and it starts with the fact that most exports do not use NULL for missing at all. They use the empty string, or `N/A`, or the literal text `NULL`, or a dash, or a zero, or `-1`, or `9999`, or `1900-01-01`. Every one of these is a value that a query will happily compare, sum and average.\n\nFind them by looking at the tail of the value list, as the earlier cleaning lesson showed, and then at the round numbers: a spike of `0` in an income column, a cluster at `-1` in an age column, a date of 1 January 1900 in a column of birthdays. Then convert each to a real NULL in the clean view, and count each kind separately:\n\n```sql\nCASE WHEN income_text IN ('', 'N/A', 'NULL', '-', '0', '-1', '9999') THEN NULL\n     ELSE income_text::numeric END AS income\n```\n\nThe counts matter because the sentinels often mean different things. In one system `-1` means \"customer refused to say\" and the empty string means \"the field did not exist when this account was created\". Those are different facts, and the next section is about why.\n\n## Four reasons a value is missing\n\n- **Not applicable.** A spouse's name for someone unmarried. There is no value and never will be.\n- **Not collected.** A field added to the form in 2025; every account from before has nothing. Missingness is a function of account age.\n- **Refused or skipped.** The customer left it blank. People who skip an income question are not a random sample of people.\n- **Lost.** A migration dropped it, an integration failed for a week. Missingness is a function of a date range.\n\nThe treatment differs by reason. Not-applicable values should stay NULL and be handled with `CASE`. Not-collected values can often be recovered from another source. Lost values can be identified by date and the affected period excluded or flagged. Refused values are the dangerous ones, because they are missing *because of* what they would have been, and no arithmetic on the rows that remain can undo that.\n\n## What filling in does to the numbers\n\nA model or a chart needs a number in every cell, so the temptation is to fill the gaps. Each fill has a mechanical consequence.\n\nFill with **zero** and the average falls, and a histogram grows a spike at zero that describes nobody. Fill with the **mean** and the average is unchanged, but the spread shrinks, because every filled row sits exactly on the centre, and anything that depends on variability (a confidence interval, a correlation, a model's certainty) is now overstated. Fill with the **median** and the same happens, slightly less. Fill by **carrying the last value forward** in a time series and a sensor that died looks like it is reporting a steady reading.\n\nThe fill that loses least is the one that keeps the fact of the missingness:\n\n```sql\ncoalesce(income, (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM customers))\n  AS income_filled,\n(income IS NULL)::int AS income_missing\n```\n\nTwo columns instead of one. The model can learn that the missing flag matters, which it often does, since customers who skip the income question behave differently from those who answer. A report can show the rate of missingness next to the average. The information was never thrown away.\n\n## Dropping rows and dropping columns\n\nA column that is 90% missing is usually a column to drop, unless the 10% is a specific, meaningful group. A row with one missing value is usually a row to keep, with the flag above. Dropping every row that has any missing value is the default in many tools and the worst option in most datasets, because missingness is rarely random. If customers who churn stop filling in the survey, dropping the incomplete rows drops the churners, and the retention figure computed on what remains is a figure about the loyal.\n\nThe test is simple: compare the rows you would drop with the rows you would keep, on a column you care about. If the two groups differ, dropping is not neutral.\n\n## Where each decision lives\n\nThe raw table keeps the sentinels. The clean view converts them to NULL and keeps NULL. The feature table, which is two lessons away, is where filling happens, next to a flag, because that is the only place a filled value is needed and the only place the filling rule can be stated once. Reports read the clean view and decide, per query, what an unknown should do, as the NULL lesson said.\n\n> Figure: Two hundred people skipped the income question. Four ways to report it. Bars (rupees, mean monthly income over 1,000 respondents): The 800 who answered 47200; Gaps filled with zero 37760; Gaps filled with the mean 47200; Gaps filled with the median 46160. Eight hundred people answered, with a mean of 47,200 and a median of 42,000. Filling with zero invents two hundred destitute respondents and drags the mean down by a fifth. Filling with the mean leaves the mean alone and quietly shrinks the spread, because every filled row sits exactly on the centre."},"check":{"question":"A team fills every missing `income` with the column's mean before training a model and building a report. Which consequence follows from the mechanism of that choice?","options":[{"text":"The average income in the report rises, because the mean of a partially filled column is always above the mean of the values that were present, since the blanks were disproportionately low earners.","correct":false,"tempting":"Filling with the mean leaves the mean exactly where it was; that is the one statistic it does not move."},{"text":"The model gains accuracy, because the filled rows now carry the most representative value the column can have.","correct":false,"tempting":"Filled rows carry no information about the customer; they carry the column's centre, and the model cannot tell a real average earner from a blank."},{"text":"The spread of income shrinks, since every filled row sits exactly at the centre, so anything relying on variability is overstated, and the fact of missingness is lost unless a flag is kept.","correct":true},{"text":"The database refuses the update, because `coalesce` with an aggregate subquery is not allowed inside a view that feeds a model.","correct":false,"tempting":"A scalar subquery inside coalesce is ordinary SQL and runs anywhere; the concern is statistical, not syntactic."}]},"prev":{"slug":"validating-a-cleaned-table","title":"Assertions: queries that return no rows when everything is right","url":"https://addaly.com/learn/data-and-sql/validating-a-cleaned-table"},"next":{"slug":"categories-and-encoding","title":"Categories: canonical values, mapping tables and encoding for a model","url":"https://addaly.com/learn/data-and-sql/categories-and-encoding"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Missing values: NULL, empty, N/A, zero and minus one', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/missing-values-honestly (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Missing values: NULL, empty, N/A, zero and minus one","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"Sentinels like empty strings, N/A, zero and minus one are values a query will happily average, so convert them to NULL, keep a flag for missingness, and fill only in the feature table, because every fill shrinks variance or invents a cluster.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"bars","unit":"rupees, mean monthly income over 1,000 respondents","title":"Two hundred people skipped the income question. Four ways to report it","series":[{"label":"The 800 who answered","value":47200},{"label":"Gaps filled with zero","value":37760,"highlight":true},{"label":"Gaps filled with the mean","value":47200},{"label":"Gaps filled with the median","value":46160}],"caption":"Eight hundred people answered, with a mean of 47,200 and a median of 42,000. Filling with zero invents two hundred destitute respondents and drags the mean down by a fifth. Filling with the mean leaves the mean alone and quietly shrinks the spread, because every filled row sits exactly on the centre."},"alt":"Figure: Two hundred people skipped the income question. Four ways to report it. Bars (rupees, mean monthly income over 1,000 respondents): The 800 who answered 47200; Gaps filled with zero 37760; Gaps filled with the mean 47200; Gaps filled with the median 46160. Eight hundred people answered, with a mean of 47,200 and a median of 42,000. Filling with zero invents two hundred destitute respondents and drags the mean down by a fifth. Filling with the mean leaves the mean alone and quietly shrinks the spread, because every filled row sits exactly on the centre."}},"blocks":[{"t":"h2","id":"five-spellings-of-we-do-not-know","runs":[{"text":"Five spellings of “we do not know”"}]},{"t":"p","runs":[{"text":"The NULL lesson covered how a database compares an unknown. This one is about what to do when a column full of unknowns arrives, and it starts with the fact that most exports do not use NULL for missing at all. They use the empty string, or "},{"text":"N/A","code":true},{"text":", or the literal text "},{"text":"NULL","code":true},{"text":", or a dash, or a zero, or "},{"text":"-1","code":true},{"text":", or "},{"text":"9999","code":true},{"text":", or "},{"text":"1900-01-01","code":true},{"text":". Every one of these is a value that a query will happily compare, sum and average."}]},{"t":"p","runs":[{"text":"Find them by looking at the tail of the value list, as the earlier cleaning lesson showed, and then at the round numbers: a spike of "},{"text":"0","code":true},{"text":" in an income column, a cluster at "},{"text":"-1","code":true},{"text":" in an age column, a date of 1 January 1900 in a column of birthdays. Then convert each to a real NULL in the clean view, and count each kind separately:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CASE WHEN income_text IN ('', 'N/A', 'NULL', '-', '0', '-1', '9999') THEN NULL\n     ELSE income_text::numeric END AS income"},{"t":"p","runs":[{"text":"The counts matter because the sentinels often mean different things. In one system "},{"text":"-1","code":true},{"text":" means “customer refused to say” and the empty string means “the field did not exist when this account was created”. Those are different facts, and the next section is about why."}]},{"t":"h2","id":"four-reasons-a-value-is-missing","runs":[{"text":"Four reasons a value is missing"}]},{"t":"ul","items":[[{"text":"Not applicable.","b":true},{"text":" A spouse’s name for someone unmarried. There is no value and never will be."}],[{"text":"Not collected.","b":true},{"text":" A field added to the form in 2025; every account from before has nothing. Missingness is a function of account age."}],[{"text":"Refused or skipped.","b":true},{"text":" The customer left it blank. People who skip an income question are not a random sample of people."}],[{"text":"Lost.","b":true},{"text":" A migration dropped it, an integration failed for a week. Missingness is a function of a date range."}]]},{"t":"p","runs":[{"text":"The treatment differs by reason. Not-applicable values should stay NULL and be handled with "},{"text":"CASE","code":true},{"text":". Not-collected values can often be recovered from another source. Lost values can be identified by date and the affected period excluded or flagged. Refused values are the dangerous ones, because they are missing "},{"text":"because of","i":true},{"text":" what they would have been, and no arithmetic on the rows that remain can undo that."}]},{"t":"h2","id":"what-filling-in-does-to-the-numbers","runs":[{"text":"What filling in does to the numbers"}]},{"t":"p","runs":[{"text":"A model or a chart needs a number in every cell, so the temptation is to fill the gaps. Each fill has a mechanical consequence."}]},{"t":"p","runs":[{"text":"Fill with "},{"text":"zero","b":true},{"text":" and the average falls, and a histogram grows a spike at zero that describes nobody. Fill with the "},{"text":"mean","b":true},{"text":" and the average is unchanged, but the spread shrinks, because every filled row sits exactly on the centre, and anything that depends on variability (a confidence interval, a correlation, a model’s certainty) is now overstated. Fill with the "},{"text":"median","b":true},{"text":" and the same happens, slightly less. Fill by "},{"text":"carrying the last value forward","b":true},{"text":" in a time series and a sensor that died looks like it is reporting a steady reading."}]},{"t":"p","runs":[{"text":"The fill that loses least is the one that keeps the fact of the missingness:"}]},{"t":"code","lang":"sql","label":"SQL","text":"coalesce(income, (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM customers))\n  AS income_filled,\n(income IS NULL)::int AS income_missing"},{"t":"p","runs":[{"text":"Two columns instead of one. The model can learn that the missing flag matters, which it often does, since customers who skip the income question behave differently from those who answer. A report can show the rate of missingness next to the average. The information was never thrown away."}]},{"t":"h2","id":"dropping-rows-and-dropping-columns","runs":[{"text":"Dropping rows and dropping columns"}]},{"t":"p","runs":[{"text":"A column that is 90% missing is usually a column to drop, unless the 10% is a specific, meaningful group. A row with one missing value is usually a row to keep, with the flag above. Dropping every row that has any missing value is the default in many tools and the worst option in most datasets, because missingness is rarely random. If customers who churn stop filling in the survey, dropping the incomplete rows drops the churners, and the retention figure computed on what remains is a figure about the loyal."}]},{"t":"p","runs":[{"text":"The test is simple: compare the rows you would drop with the rows you would keep, on a column you care about. If the two groups differ, dropping is not neutral."}]},{"t":"h2","id":"where-each-decision-lives","runs":[{"text":"Where each decision lives"}]},{"t":"p","runs":[{"text":"The raw table keeps the sentinels. The clean view converts them to NULL and keeps NULL. The feature table, which is two lessons away, is where filling happens, next to a flag, because that is the only place a filled value is needed and the only place the filling rule can be stated once. Reports read the clean view and decide, per query, what an unknown should do, as the NULL lesson said."}]}],"check":{"question":[{"text":"A team fills every missing "},{"text":"income","code":true},{"text":" with the column’s mean before training a model and building a report. Which consequence follows from the mechanism of that choice?"}],"options":[{"text":[{"text":"The average income in the report rises, because the mean of a partially filled column is always above the mean of the values that were present, since the blanks were disproportionately low earners."}],"correct":false,"tempting":[{"text":"Filling with the mean leaves the mean exactly where it was; that is the one statistic it does not move."}]},{"text":[{"text":"The model gains accuracy, because the filled rows now carry the most representative value the column can have."}],"correct":false,"tempting":[{"text":"Filled rows carry no information about the customer; they carry the column’s centre, and the model cannot tell a real average earner from a blank."}]},{"text":[{"text":"The spread of income shrinks, since every filled row sits exactly at the centre, so anything relying on variability is overstated, and the fact of missingness is lost unless a flag is kept."}],"correct":true,"tempting":null},{"text":[{"text":"The database refuses the update, because "},{"text":"coalesce","code":true},{"text":" with an aggregate subquery is not allowed inside a view that feeds a model."}],"correct":false,"tempting":[{"text":"A scalar subquery inside coalesce is ordinary SQL and runs anywhere; the concern is statistical, not syntactic."}]}],"order":[3,2,1,0],"why":null},"prev":{"slug":"validating-a-cleaned-table","title":"Assertions: queries that return no rows when everything is right","minutes":8},"next":{"slug":"categories-and-encoding","title":"Categories: canonical values, mapping tables and encoding for a model","minutes":9}}}