Data Cleaning Patterns

Fill Missing Values

Use COALESCE

COALESCE replaces NULLs with the first available fallback value.

Program

Play the script to choose a default city for rows with missing profile data.

fill_missing_values.sql 
CREATE TABLE profiles (name TEXT, city TEXT, visits INTEGER);
INSERT INTO profiles VALUES ('Ada', 'Austin', 3), ('Lin', NULL, 2), ('Nia', NULL, 5);
WITH params(default_city) AS (VALUES ()) SELECT name, COALESCE(city, (SELECT default_city FROM params)) AS clean_city, visits FROM profiles ORDER BY name;

CREATE TABLE profiles (name TEXT, city TEXT, visits INTEGER);
INSERT INTO profiles VALUES ('Ada', 'Austin', 3), ('Lin', NULL, 2), ('Nia', NULL, 5);
WITH params(default_city) AS (VALUES ()) SELECT name, COALESCE(city, (SELECT default_city FROM params)) AS clean_city, visits FROM profiles ORDER BY name;

CREATE TABLE profiles (name TEXT, city TEXT, visits INTEGER);
INSERT INTO profiles VALUES ('Ada', 'Austin', 3), ('Lin', NULL, 2), ('Nia', NULL, 5);
WITH params(default_city) AS (VALUES ()) SELECT name, COALESCE(city, (SELECT default_city FROM params)) AS clean_city, visits FROM profiles ORDER BY name;
COALESCE `COALESCE(city, fallback)` returns `city` unless it is NULL.
missing value NULL means the value is unknown, not an empty string.
default value The selector changes the fill value used for missing cities.