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.

default_city
fill_missing_values.sql
Replay: real traced execution (multi-file project)
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 ('Unknown')) 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 ('Remote')) 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 ('Unassigned')) SELECT name, COALESCE(city, (SELECT default_city FROM params)) AS clean_city, visits FROM profiles ORDER BY name;

  1. tables ← 1 row

    1CREATE TABLE profiles (name TEXT, city TEXT, visits INTEGER);2INSERT INTO profiles VALUES ('Ada', 'Austin', 3), ('Lin', NULL, 2), ('Nia', NULL, 5);
    values this step1 rowtables

  2. profiles ← 3 rows

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

  3. result ← 3 rows

    2INSERT INTO profiles VALUES ('Ada', 'Austin', 3), ('Lin', NULL, 2), ('Nia', NULL, 5);3WITH params(default_city) AS (VALUES ('Unknown')) SELECT name, COALESCE(city, (SELECT default_city FROM params)) AS clean_city, visits FROM profiles ORDER BY name;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE profiles (name TEXT, city TEXT, visits INTEGER);2INSERT INTO profiles VALUES ('Ada', 'Austin', 3), ('Lin', NULL, 2), ('Nia', NULL, 5);
    values this step1 rowtables

  2. profiles ← 3 rows

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

  3. result ← 3 rows

    2INSERT INTO profiles VALUES ('Ada', 'Austin', 3), ('Lin', NULL, 2), ('Nia', NULL, 5);3WITH params(default_city) AS (VALUES ('Remote')) SELECT name, COALESCE(city, (SELECT default_city FROM params)) AS clean_city, visits FROM profiles ORDER BY name;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE profiles (name TEXT, city TEXT, visits INTEGER);2INSERT INTO profiles VALUES ('Ada', 'Austin', 3), ('Lin', NULL, 2), ('Nia', NULL, 5);
    values this step1 rowtables

  2. profiles ← 3 rows

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

  3. result ← 3 rows

    2INSERT INTO profiles VALUES ('Ada', 'Austin', 3), ('Lin', NULL, 2), ('Nia', NULL, 5);3WITH params(default_city) AS (VALUES ('Unassigned')) SELECT name, COALESCE(city, (SELECT default_city FROM params)) AS clean_city, visits FROM profiles ORDER BY name;
    values this step3 rowsresult
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.