{"id":491,"date":"2009-12-27T12:49:34","date_gmt":"2009-12-27T12:49:34","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=491"},"modified":"2023-12-29T06:24:21","modified_gmt":"2023-12-29T13:24:21","slug":"avoid-displaying-null-values-by-mapping-to-other-values","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/avoid-displaying-null-values-by-mapping-to-other-values\/","title":{"rendered":"How To Map NULL Values To Other Meaningful Values"},"content":{"rendered":"\n

Summary<\/strong>:<\/em> in this tutorial, you will learn how to map NULL<\/code> values to other meaningful values.<\/p>\n\n\n\n

Dr.E.F.Codd<\/a>, who is the creator of the relational model for the database, introduced the NULL<\/code> concept in the relational database theory. According to Dr.E.F.Codd, NULL<\/code> means unknown value or missing information.<\/p>\n\n\n\n

MySQL also supports NULL<\/code> that represents the concept of missing or inapplicable information.<\/p>\n\n\n\n

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

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

The syntax of the IF function<\/a> is as follows:<\/p>\n\n\n

IF(exp,exp_result1,exp_result2);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the exp<\/code> evaluates to TRUE<\/code> (When exp <> 0<\/code>  and exp <> NULL<\/code> ), the IF<\/code> function returns the value of the exp_result1<\/code> otherwise, it returns the value of exp_result2<\/code> . <\/em><\/p>\n\n\n\n
The returned value of the IF<\/code> function can be a string or a number, depending on the exp_result1<\/code> and exp_result2<\/code> expressions.<\/em><\/p>\n\n\n\n
Let’s practice with some examples to get a better understanding.<\/p>\n\n\n\n
The following is the partial data in the customers<\/code> table which includes customername<\/code> state<\/code> and country<\/code> :<\/p>\n\n\n
SELECT<\/span> \n    customername, state, country\nFROM<\/span>\n    customers\nORDER<\/span> BY<\/span> country;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
From the result set above, you see that the state <\/em>values are not available for some customers. You can use the IF<\/code> function to display NULL<\/code> value as N\/A<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    customername, IF<\/span>(state IS<\/span> NULL<\/span>, 'N\/A'<\/span>, state) state, country\nFROM<\/span>\n    customers\nORDER<\/span> BY<\/span> country;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Besides the IF function, MySQL provides the IFNULL <\/em><\/a>function that allows you to handle NULL<\/code> values directly. The following is the syntax of the IFNULL<\/code> function:<\/p>\n\n\n
IFNULL(exp,exp_result);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The IFNULL<\/code> function returns the value of the exp_result<\/code> expression if the exp<\/code> evaluates to a NULL<\/code> value, otherwise, it returns the value of the exp<\/code> expression.<\/p>\n\n\n\n
The following query uses the IFNULL<\/code> function to display NULL<\/code> as unknown as follows:<\/p>\n\n\n
SELECT<\/span> customername, \n       IFNULL<\/span>(state,\"N\/A\"<\/span>) state, \n       country\nFROM<\/span> customers\nORDER<\/span> BY<\/span> country;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the IF<\/code>  and IFNULL<\/code> functions to map the NULL values onto other more meaningful values for presenting data in a readable manner.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a> <\/p>\n\n\n\n
Try It Out<\/a> <\/p>\n\n\n\n
Try It Out<\/a> <\/p>\n\n\n\n
Try It Out<\/a> <\/p>\n\n\n\n
We will work with the customers<\/code> table in the sample database<\/a>.<\/p>\n\n\n\n