How to replace NULL with 0 in SQL Server?

There are few different ways to replace NULL with 0 in SQL Server. Let us go through them one by one.

1. Using ISNULL function

The easiest and the straightforward way to replace NULL with 0 is by using the ISNULL function.Here is an illustration of how to use it.

DECLARE @Int_Val INT;
SET @Int_Val = NULL;
SELECT ISNULL(@Int_Val, 0) AS Result;

ISNULL can be used to replace NULL with any value.

2. Using COALESCE function

The next method is by using the COALESCE function. COALESCE was introduced in SQL Server 2008. We can use the COALESCE function similar to the ISNULL. However, COALESCE is more flexible. It can accept more than one argument and return the first non-null value from the arguments. Let’s see an example:

DECLARE @Int_Val INT;
SET @Int_Val = NULL;
SELECT COALESCE(@Int_Val, 0) AS Result;

3. Using CASE statement

Using CASE is another way to replace NULL with another value. Using CASE statement is not an ideal way to replace NULL, however it’s possible. Here is an illustration:

DECLARE @Int_Val INT;
SET @Int_Val = NULL;

SELECT 
	CASE 
		WHEN @Int_Val IS NULL THEN 0 
		ELSE @Int_Val 
	END AS Result; 

Reference

• More about ISNULL at Microsoft Learn.
• More about COALESCE at Microsoft Learn.