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 represent 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). In order to do this, you can use the IF function.

The syntax of the IF function is as follows:

1	IF(exp,exp_result1,exp_result2);

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 that includes customername state and country :

1 2 3 4 5

SELECT     customername, state, country FROM     customers ORDER BY country;

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:

1 2 3 4 5

SELECT     customername, IF(state IS NULL, 'N/A', state) state, country FROM     customers ORDER BY country;

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:

1	IFNULL(exp,exp_result);

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

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

1 2 3 4 5

SELECT customername,        IFNULL(state,"N/A") state,        country FROM customers ORDER BY country;

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.