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:
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 that includes customername
state
and country
:
SELECT
customername, state, country
FROM
customers
ORDER BY country;
Code language: SQL (Structured Query Language) (sql)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-NULL-values-example.jpg?resize=334%2C198)
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)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-NULL-as-NA-value.jpg?resize=335%2C223)
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)
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)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-NULL-with-IFNULL-function.jpg?resize=333%2C223)
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.