How to Replace NULLs with Previous Non-NULL Values in SQL?

Retrieving Missing Values in SQL: Replacing NULLs with Previous Known Values

Data tables often contain missing values represented by NULLs. To optimize analysis and maintain data integrity, it becomes crucial to replace NULLs with meaningful values. One common strategy is to replace each NULL with the last known non-NULL value in a preceding row.

To achieve this in SQL Server, consider the following solution:

DECLARE @Table TABLE(
        ID INT,
        Val INT
)

INSERT INTO @Table (ID,Val) SELECT 1, 3
INSERT INTO @Table (ID,Val) SELECT 2, NULL
INSERT INTO @Table (ID,Val) SELECT 3, 5
INSERT INTO @Table (ID,Val) SELECT 4, NULL
INSERT INTO @Table (ID,Val) SELECT 5, NULL
INSERT INTO @Table (ID,Val) SELECT 6, 2

SELECT  *,
        ISNULL(Val, (SELECT TOP 1 Val FROM @Table WHERE ID < t.ID AND Val IS NOT NULL ORDER BY ID DESC))
FROM    @Table t

This query works as follows:

• The inner query retrieves the last non-NULL value for each row by using a subquery with an ORDER BY clause.
• The outer query joins the table with itself using the ID column.
• The ISNULL function is used to check for NULLs. If the current row's Val column is NULL, it is replaced with the value retrieved by the subquery.

The above is the detailed content of How to Replace NULLs with Previous Non-NULL Values in SQL?. For more information, please follow other related articles on the PHP Chinese website!