Avoid Displaying NULL Values by Mapping to Other Values

The NULL was introduced by the creator of relational database model, Dr.E.F.Codd. In relational database management systems, NULL means unknown value or the value does not exist in database. MySQL supports NULL to represents the concept of missing information and inapplicable information. Sometimes, we have to display the stored data which has NULL value to application user interface. Therefore it doesn't make sense to display NULL value because it is missing information. So we have to display it as another values such as unknown, missing or not available (N/A). One technique to solve this problem is using IF function. The syntax of IF function is simple as follows:

 IF(exp,exp_result1,exp_result2)   

If the exp is TRUE (when exp <> 0 and exp <> NULL), the IF() function will return exp_result1 otherwise it returns exp_result2. The returned value of IF() function can be a string or a numeric depends on the exp_result1 and exp_result.

Let's practice with an example. We are now working with productLines table. Here are some data from it:

productLine       image 
----------------  ------
Classic Cars      (NULL)
Motorcycles       (NULL)
Planes            (NULL)
Ships             (NULL)
Trains            (NULL)
Trucks and Buses  (NULL)
Vintage Cars      (NULL)

As you see, the image is not availble for each product line, so we can use IF to display N/A image as follows:

 SELECT productLine,
 IF(image IS NULL,"N/A",image) image
 FROM productLines  

Here is the result

productLine       image 
----------------  ------
Classic Cars      N/A   
Motorcycles       N/A   
Planes            N/A   
Ships             N/A   
Trains            N/A   
Trucks and Buses  N/A   
Vintage Cars      N/A   

MySQL also supports IFNULL() function to allow you to deal with NULL value directly. Here is the syntax:

 
 IFNULL(exp,exp_result)  

IFNULL function returns exp_result if the exp is NULL, otherwise it returns the exp.

You can achieve the same result in above example by using IFNULL function instead of IF function. Here is the query:

 SELECT productLine,
 IFNULL(image,"N/A") image
 FROM productLines  

• « Previous page
• Next page »