Replace All NULL Values with 0 in MySQL

When you execute a Left Join or a UNION query, DBMS places NULL where there is no match in the set. In this tutorial, we are going to see different options to replace NULL with 0 or any other value to be filled in the column.

There are 3 options:

• COALESCE Function
• IFNULL Function
• CASE statement

 

Example:

Let’s take an examples. In this tutorial, we are going to use the “Clients” table.

SELECT Age FROM Clients;

 

COALESCE Function

COALESCE() will return the first non-NULL value in the list, or NULL if there is no non-NULL value.

SELECT COALESCE(Age, '0') AS Age FROM Clients;

 

IFNULL Function

IFNULL function can also be used to replace NULL values with another value. It simply checks if the value of its first argument is NULL, so it will be replaced by the value of the second argument.

SELECT IFNULL(Age, '0') AS Age FROM Clients;

 

 

CASE statement

Another way to replace NULL values with another value, is to use CASE expression:

SELECT 
    CASE 
        WHEN Age IS NOT NULL THEN Age 
        ELSE '0' 
    END AS Result
FROM Clients;

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More