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. If you want to learn more about SQL, Please refer to our SQL Course.