Search⌘ K
AI Features
Home
Courses
Practical Data Analysis with SQL

Back Filling and Forward Filling

Explore methods to handle missing values in SQL data analysis by learning to use back filling and forward filling techniques. Understand how to fill gaps using the last known or next known data points, and apply functions like COALESCE and subqueries to maintain data integrity for accurate results.

We'll cover the following...

Overview

Missing data can result from legitimate causes or bugs, but either way, we have to deal with it. To make the analysis easier and to reduce the risk of potential errors caused by mishandling missing values, we sometimes want to fill missing data with other values.

Filling constant values

The most straightforward way to fill in missing data is to replace it with a constant value. To replace NULL values with a constant, we can use CASE:

PostgreSQL
SELECT 
  n,
  v,
  CASE WHEN v IS NULL THEN 'X' ELSE v END AS adjusted_value
FROM (VALUES
  (1, 'A' ),
  (2, 'B' ),
  (3, null),
  (4, 'D' ),
  (5, null),
  (6, null),
  (7, 'G' )
) AS t(n, v);

The CASE expression returns the constant value X when the value in the column v is ...