When you insert the data into the table but skip some values for a particular column(S), NULL will get inserted into it. The NULL indicates an unknown value or missing value.
On the user side, it is very important to show only meaningful data and not NULL. You can show messages such as N/A, no data, or missing information.
In this tutorial, we will learn why is it important to map NULL values to other meaningful values and how to perform the mapping.
Why is it Important to Map NULL Values?
When creating an application, it is vital to display meaningful data to the user. For example, let’s suppose you give an interface to the user ‘A’ to fill in the profile details. However, ‘A’ might not fill in each and every detail. So, whenever other users see the profile of the user ‘A’, they must see either whitespace or some meaningful message such as ‘No Data’.
The NULL notion was brought into relational database theory by Dr. E.F.Codd, the developer of the relational model for databases. NULL stands for unknown value or missing information, according to Dr. E.F.Codd.
You must represent NULL values as other values such as unknown, missing, or not available (N/A) to make the reports more readable and understandable.
We can perform this mapping using the IF and IFNULL function in MySQL. There might be multiple ways to achieve it, but we will see only the simplest and most effective ways.
Creating a table
Before moving to the examples, we have to create a table with some NULL values in it.
CREATE TABLE stud_details(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
reg_date DATETIME DEFAULT NOW()
);Code language: SQL (Structured Query Language) (sql)Here, we have created four columns- id, name, age and reg_date. The id and reg_date columns can not be NULL, but name and age can be. So, we will leave some values for the age column blank which will represent NULL.
Let’s insert values into the table now.
INSERT INTO stud_details(name,age)
VALUES("Raj", 12),("John", 14),
("Roshan", 15);
INSERT INTO stud_details(name)
VALUES("Manju"),("Atlas"),("Bob"),
("Dipak"), ("Farhan");Code language: SQL (Structured Query Language) (sql)Let’s display the table to check if we are getting the expected output.
SELECT * FROM stud_details;Code language: SQL (Structured Query Language) (sql)
As you can see, some rows for the age column are showing NULL values.
Mapping NULL Values Using IF Function
IF function is the best way to map NULL values to any other meaningful value.
The syntax of the IF function is-
IF(exp,exp_result1,exp_result2);Code language: SQL (Structured Query Language) (sql)Where,
- exp- It is the expression that will be evaluated to find the result. For ex- 100>20, a<b
- exp_result1 – When exp becomes true, the IF function returns the value of exp_result1.
- exp_result2 – When exp becomes false, the IF function returns the value of exp_result2.
Let’s now write a query to display the table data that will give values other than NULL.
We will map the NULL value with the N/A.
SELECT id, name, IF(age IS NULL,'N/A',age)as age,
reg_date FROM stud_details;Code language: SQL (Structured Query Language) (sql)Here, we check if the age is NULL. If it’s NULL then we display ‘N/A’, otherwise, we display the original value.
Let’s execute the query and see the result.
As you can see, we have received the expected output.
Mapping NULL Values Using IFNULL Function
Besides the IF function, MySQL provides us with the IFNULL function which is a shorter version of checking if the value is NULL inside the IF function.
Syntax of IFNULL function is-
IFNULL(exp,exp_result);Code language: SQL (Structured Query Language) (sql)Here, the function directly checks if the given expression is NULL. The exp might be an expression or a single variable as well.
If the expression evaluates to the NULL, the function will return the value of the exp_result. Else, the original value of that column will be displayed.
Let’s write a query to map NULL values to ‘N/A’ using the IFNULL function.
SELECT id, name, IFNULL(age,'N/A') as age,
reg_date FROM stud_details;Code language: SQL (Structured Query Language) (sql)
As you can see, we have received the expected output.
Conclusion
In this tutorial, we have learned how to map NULL values to other meaning values using IF and IFNULL functions. Both are the easiest ways to perform mapping and it’s quite useful when developing an application.