How to replace NULL values with ‘0’ in SQL

In this blog, we will learn how to replace null values with 0 in SQL, If we want to replace the null values with 0, we can use the ISNULL function. Let’s learn this concept in detail.

Table of Content

• Replace null with 0 in SQL
  • Syntax of ISNULL
  • Example: Replacing NULL with 0
• Another method to replace null with 0 in SQL
  • Using CASE Statement
  • Using COALESCE
• Conclusion

Replace null with 0 in SQL

We can replace the null value with 0 in SQL with the help of ISNULL function. Let’s learn this concept with an example.

Syntax of ISNULL

ISNULL(expression, replacement_value)

Where,

• expression is the column name or value to check for NULL.
• replacement_value is the value that should be returned if the expression is NULL.

Example: Replacing NULL with 0

Suppose we have a table named Sales, that contains sales_Id and Amount of the sales. Our task is to replace all the NULL with 0.

Table: Sales

Query:

SELECT id, ISNULL(amount, 0) AS amount
FROM sales;

Output:

Another method to replace null with 0 in SQL

Here are some of the other methods that can replace null with 0 in SQL.

Using CASE Statement

Syntax:

COALESCE(column_name, 0)

Query:

SELECT Sales_Id, COALESCE(amount, 0) AS amount

FROM sales;

Using COALESCE

Syntax:

CASE WHEN column_name IS NULL THEN 0 ELSE column_name END

Example:

SELECT Sales_Id, 
       CASE 
           WHEN amount IS NULL THEN 0 
           ELSE amount 
       END AS amount
FROM sales;

If you want to learn about SQL, you may refer interview questions: SQL Interview Questions and Answers.

Conclusion

In conclusion, we have learned how to replace null with 0 in SQL. Also, we have learned what are the other methods to perform the same operations in SQL, including techniques to Delete Null Values in SQL effectively. If you want to learn more about SQL, Please refer to our SQL Course.