How To Map NULL Values To Other Meaningful Values

Summary: in this tutorial, you will learn how to map NULL values to other meaningful values.

Dr.E.F.Codd, who is the creator of the relational model for the database, introduced the NULL concept in the relational database theory. According to Dr.E.F.Codd, NULL means unknown value or missing information.

MySQL also supports NULL that represents the concept of missing or inapplicable information.

In the database table, you store data that contains NULL values. When you present the data to the users in the form of reports, it doesn’t make sense to display the NULL values.

To make the reports more readable and understandable, you have to display NULL values as other values such as unknown, missing, or not available (N/A). To do this, you can use the IF function.

The syntax of the IF function is as follows:

IF(exp,exp_result1,exp_result2);Code language: SQL (Structured Query Language) (sql)

If the exp evaluates to TRUE (When exp <> 0 and exp <> NULL ), the IF function returns the value of the exp_result1 otherwise, it returns the value of exp_result2 .

The returned value of the IF function can be a string or a number, depending on the exp_result1 and exp_result2 expressions.

Let’s practice with some examples to get a better understanding.

We will work with the customers table in the sample database.

The following is the partial data in the customers table which includes customername state and country :

SELECT 
    customername, state, country
FROM
    customers
ORDER BY country;Code language: SQL (Structured Query Language) (sql)

Try It Out

From the result set above, you see that the state values are not available for some customers. You can use the IF function to display NULL value as N/A:

SELECT 
    customername, IF(state IS NULL, 'N/A', state) state, country
FROM
    customers
ORDER BY country;Code language: SQL (Structured Query Language) (sql)

Try It Out

Besides the IF function, MySQL provides the IFNULL function that allows you to handle NULL values directly. The following is the syntax of the IFNULL function:

IFNULL(exp,exp_result);Code language: SQL (Structured Query Language) (sql)

Try It Out

The IFNULL function returns the value of the exp_result expression if the exp evaluates to a NULL value, otherwise, it returns the value of the exp expression.

The following query uses the IFNULL function to display NULL as unknown as follows:

SELECT customername, 
       IFNULL(state,"N/A") state, 
       country
FROM customers
ORDER BY country;Code language: SQL (Structured Query Language) (sql)

Try It Out

In this tutorial, you have learned how to use the IF and IFNULL functions to map the NULL values onto other more meaningful values for presenting data in a readable manner.