How to Handle NULL Values in MySQL with Examples

Hello Programmers, I hope you guys are doing well. In this article, you will learn everything about how to handle Null values in MySQL database tables with the help of proper explanations and examples.

MySQL offers some functions that are responsible for handling Null values, Throughout this article, we will explore all those functions.

handling Null values becomes one of the tedious tasks for beginners when they are working on any kind of real-life project where MySQL is playing a major role. Obviously, It is a popular database and it plays important role in a kind of dynamic application. Before going to further into this article we will see a brief description of Null values.

Headings of Contents

1 What is Null Value?
2 Why do we need to handle Null values in MySQL?
3 Handling Null values in MySQL
- 3.1 Concatenate column including NULL Values
- 3.2 Handling NULL values of decimal-based column
4 Update NULL Values using MySQL UPDATE Statement
5 MySQL ORDER BY With NULL Values
6 Summary

What is Null Value?

In MySQL, A Null value means nothing, It is completely different from any other value or an empty string. It does not equal any other value like 0,1,2, and so on. In fact, It does not equal any string or an empty string.

If you compare the Null value with another value the output will be always Null because Null means no value or nothing.

Mostly, we use a Null value to indicate the data is missing, unknown, and not an application. The behavior of a column with a Null value is defined during the creation of the column or altering the column.

When you create a table, you can specify whether a column accepts a Null value by using a NULL keyword or not accepting any null value by using a NOT NULL Keyword.

Why do we need to handle Null values in MySQL?

Handling Null values in MySQL or any other language like Python, etc is one of the most important because it represents the nothing or missing value that’s why it is compulsory to clean the Null value before using the data.

When you will compare any valid value to a Null value, the result will always be Null, therefore, it will raise problems in selecting the data, and obviously, this has a bad effect on the result set.

MySQL offers multiple ways to handle Null values within a table which will explore further.

Note:- It is not Possible to compare Null values by using comparioson operators like =, <, >, <>, etc.

To handle Null values in MySQL, we should have a table at least has some records. For that reason, I am going to create a table called student.

First, we need to login into MySQL by using the following command. You can also login into Workbench to handle NULL Values in MySQL

mysql -u root -p

Use the following statement to create a table. I am about to create a table called student. It will be just for demonstration.

CREATE TABLE student (
    roll_no INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) DEFAULT NULL,
    college VARCHAR(50) NOT NULL,
    city VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY (roll_no)
);

As you can see, In the above statement, roll_no is the primary key therefore it does not accept any Null value because it is set to NULL NOT.

The column first_name and college column use the NOT NULL constraints that’s why this column can not accept null values, whereas the last_name, the city accepts Null values because they are used NUll constraints and If you are not inserting anything by default it will insert NULL values otherwise you can insert NULL values which will indicate the missing values.

Now, I am going to insert some records into the student table by using MySQL INSERT INTO statement.

INSERT INTO student VALUES (1,'Vishvajit','Rao','BBDU','Noida'),(2,'Jainy','Doe','BBDEC','Mumbai'),(3,'Vaishali',NULL,'BBD College',NULL),(4,'Harshita','Kumari','ITC College','Kolkata'),(5,'Vishal','Rao','City College',NULL),(6,'Karan',NULL,'IT college of engineering','Chennai'),(7,'Vishvajit','Rao','ABC Group of College',NULL),(8,'Pankaj','Sharma','ABC Group of College',NULL),(9,'John',NULL,'Delhi University','Delhi'),(10,'Veronika',NULL,'Lucknow University','Lucknow'),(11,'harsh','Mehta','University of Punjab','Punjab');

You can verify whether records are inserted or not using MySQL SELECT statement, As you can below.

Now it’s time to handle NULL values in MySQL, Let’s explore different-different ways to handle NULL values in MySQL.

Handling Null values in MySQL

There are multiple ways to handle Null values in MySQL, Here I am going to explore all possible ways to handle Null values within a table. I will use the table student that we created earlier in all the examples.

As we know, In the above table student, The last_name and city columns could accept Null values that’s why we will only work on those columns.

First of all, I will try to fetch all those students’ records whose last_name has a Null value or missing value so that we can handle all those records easily. To access will use IS NULL keyword.

mysql> SELECT * FROM student WHERE last_name IS NULL;
+---------+------------+-----------+---------------------------+---------+
| roll_no | first_name | last_name | college                   | city    |
+---------+------------+-----------+---------------------------+---------+
|       3 | Vaishali   | NULL      | BBD College               | NULL    |
|       6 | Karan      | NULL      | IT college of engineering | Chennai |
|       9 | John       | NULL      | Delhi University          | Delhi   |
|      10 | Veronika   | NULL      | Lucknow University        | Lucknow |
+---------+------------+-----------+---------------------------+---------+
4 rows in set (0.18 sec)

As you can see we have a total of 4 records whose last_name is missing and same as we can fetch all those records whose city value is Null or missing, Similarly Let’s try to fetch all those records whose last_name and city have no Null values.

Here we will use IS NOT NULL keyword to fetch all those records whose last_name and city column do not have Null values.

mysql> SELECT * FROM student WHERE last_name IS NOT NULL AND city IS NOT NULL;
+---------+------------+-----------+----------------------+---------+
| roll_no | first_name | last_name | college              | city    |
+---------+------------+-----------+----------------------+---------+
|       1 | Vishvajit  | Rao       | BBDU                 | Noida   |
|       2 | Jainy      | Doe       | BBDEC                | Mumbai  |
|       4 | Harshita   | Kumari    | ITC College          | Kolkata |
|      11 | harsh      | Mehta     | University of Punjab | Punjab  |
+---------+------------+-----------+----------------------+---------+
4 rows in set (0.00 sec)

Now you can see, This time also we have a total of 4 records that have perfect data.

let’s handle NULL values by taking different-different scenarios.

Concatenate column including NULL Values

Let’s take a scenario where we will try to create a column named full_name which will be the combination of first_name and last_name, For example, If the value of first_name and last_name is ‘Programming‘ and ‘Funda‘ Then the output will be ‘Programming Funda‘ which will represent the full_name of the student.

To concatenate the value of the first_name and last_name columns, we will use the MySQL concat() function which takes the column name as an argument and concatenates them.

**Using concat() function to concate columns**

As you can see in the above result set, A full_name column has been created having a concatenation of the first_name and last_name column but there one thing is to be noted here, In the full_name column some values are NULL. Have you ever thought, How NULL values came there, Don’t worry, I’m going to tell you?

As you can see in the above SQL SELECT statement, we have used the concat() function to concatenation the value of the first_name and last_name columns, and the concat() function will add the value of first_name and last_name column for each row and if any one of those value is NULL then the output will also be NULL, that’s why full_name column contained a NULL value for all those students whose value of last_name is NULL.

Now, I am going to handle NULL values in the above result set and this full_name will not contain NULL values because if the value of last_name will be NULL then the only value of first_name will include in the full_name column. Here I will use IFNULL() function to handle NULL values.

**Handling Null Values in concat() function**

Have you seen, How successfully we have handled all the NULL values on the full_name column just by using IFNULL() function, This is the beauty of the IFNULL() function?

CONCAT(): This is a built-in function in MySQL that is used to concatenate the value of columns or passed hardcode values. it will return the NULL If any passed value is NULL.

👉concat() function reference:- click here

Apart from concat() function, MySQL provides another function to concatenate column values which is called concat_ws().
This function is very smart in comparison to concat() function because it concatenates only valid column values which means it will concatenate only those columns that have valid data except the NULL value. It also takes a separator as the first parameter to separate all those values.

Let’s see.

**Using concat_ws() function to concate columns**

CONCAT_WS():- This is a built-in function in MYSQL that is used to concatenate the value of passed columns even if you can pass hardcoded values inside it. It takes a separator as a first parameter to separate values that passed to be concatenated. This function neglects the NULL values and only concatenates the valid column data.

👉concat_ws() function reference:- click here

Handling NULL values of decimal-based column

In the above example, we have seen the process of handling the NULL value of a string-based column but sometimes we have to handle integer and decimal-based column Null values as well, let’s take an example where will try to handle Null values of a decimal-based column.

I have a table employee having a few columns like id, first_name, last_name, and salary. I want to create a column that will represent the ‘travelling_fare‘ of the employee and ‘travelling_fare‘ of the employee will be 10 percent of their salary.

Result set without handling the NULL values:

mysql> SELECT id, first_name,last_name,salary, FLOOR((salary * 10) / 100) as 'travelling_fare' FROM employee;
+----+------------+-----------+-----------+-----------------+
| id | first_name | last_name | salary    | travelling_fare |
+----+------------+-----------+-----------+-----------------+
|  1 | Vishvajit  | Rao       |  25000.00 |            2500 |
|  2 | Vinay      | Kumar     |      NULL |            NULL |
|  3 | Minakshi   | Kumary    |  30000.00 |            3000 |
|  4 | John       | Doe       |  80000.00 |            8000 |
|  5 | Akash      | Saini     |      NULL |            NULL |
|  6 | Harshita   | Singh     |  20000.00 |            2000 |
|  7 | Ayush      | Shukla    |  23500.00 |            2350 |
|  8 | Abhishek   | Mishra    |      NULL |            NULL |
|  9 | Vaishanvi  | Mehta     |  75250.00 |            7525 |
| 10 | Mahima     | Chaudhari |      NULL |            NULL |
+----+------------+-----------+-----------+-----------------+

As you can see in the above result set, how NULL VALUES have been included in the ‘travelling_fare’ column, This is not looking good, That’s why as a Developer, SQL Developer, or database administrator it is our responsibility to handle all these NULL values.

Now, Here I will use IFNULL() function again to handle all the NULL values, I am going to show zero instead of NULL values.

Result set after handling the NULL values:

mysql> SELECT id, first_name,last_name,salary, FLOOR((ifnull(salary, 0) * 10) / 100) as 'travelling_fare' FROM employee;
+----+------------+-----------+-----------+-----------------+
| id | first_name | last_name | salary    | travelling_fare |
+----+------------+-----------+-----------+-----------------+
|  1 | Vishvajit  | Rao       |  25000.00 |            2500 |
|  2 | Vinay      | Kumar     |      NULL |               0 |
|  3 | Minakshi   | Kumary    |  30000.00 |            3000 |
|  4 | John       | Doe       |  80000.00 |            8000 |
|  5 | Akash      | Saini     |      NULL |               0 |
|  6 | Harshita   | Singh     |  20000.00 |            2000 |
|  7 | Ayush      | Shukla    |  23500.00 |            2350 |
|  8 | Abhishek   | Mishra    |      NULL |               0 |
|  9 | Vaishanvi  | Mehta     |  75250.00 |            7525 |
| 10 | Mahima     | Chaudhari |      NULL |               0 |
+----+------------+-----------+-----------+-----------------+

As you can see in the above result set, I have successfully handled the NULL values with 0.

So this is how you can use MySQL IFNULL() Null function to handle the NULL values and this function is going to be very useful when you will work with any large projects when you need to handle lakhs or millions of records.

let’s explore another way to handle NULL values.

Update NULL Values using MySQL UPDATE Statement

MySQL UPDATE statement is another way to handle the NULL values by using the SET keyword and assignment ( = ) operator.

Let’s take the above example to handle the NULL values. For example, As you can see in the above result set the second employee’s salary is NULL but after knowing the salary of employee 2, We can update the salary by using MySQL UPDATE Statement.

UPDATE employee 
SET 
    salary = 35000.00
WHERE
    id = 2

Now let’s select the above-updated employee details by using the MYSQL SELECT statement.

mysql> SELECT id, first_name,last_name, salary FROM employee WHERE id = 2;
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |
+----+------------+-----------+----------+
|  2 | Vinay      | Kumar     | 35000.00 |
+----+------------+-----------+----------+
1 row in set (0.00 sec)

MySQL ORDER BY With NULL Values

You can check NULL values by using the ORDER BY clause because MySQL ORDER BY always treats a NULL value as a lower value in comparison to other values.

By default, ORDER BY sorts the result set in ascending order, But you can order the result set n descending order as well just by applying the DESC keyword.

If you will order the result set in ascending order, NULL values will show first, and in case of order the result set in descending order NULL values will show the end of the result set.

let’s see all these theories in a practical way.

Here I will apply for the ORDER BY clause on the salary column because it has NULL values.

mysql> SELECT id, first_name,last_name, salary from employee ORDER BY salary;
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |
+----+------------+-----------+----------+
|  5 | Akash      | Saini     |     NULL |
|  8 | Abhishek   | Mishra    |     NULL |
| 10 | Mahima     | Chaudhari |     NULL |
|  6 | Harshita   | Singh     | 20000.00 |
|  7 | Ayush      | Shukla    | 23500.00 |
|  1 | Vishvajit  | Rao       | 25000.00 |
|  3 | Minakshi   | Kumary    | 30000.00 |
|  2 | Vinay      | Kumar     | 35000.00 |
|  9 | Vaishanvi  | Mehta     | 75250.00 |
|  4 | John       | Doe       | 80000.00 |
+----+------------+-----------+----------+
10 rows in set (0.16 sec)

As you can see, All the NULL Values are showing first.

let’s order the salary column in descending order.

mysql> SELECT id, first_name,last_name, salary from employee ORDER BY salary DESC;
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |
+----+------------+-----------+----------+
|  4 | John       | Doe       | 80000.00 |
|  9 | Vaishanvi  | Mehta     | 75250.00 |
|  2 | Vinay      | Kumar     | 35000.00 |
|  3 | Minakshi   | Kumary    | 30000.00 |
|  1 | Vishvajit  | Rao       | 25000.00 |
|  7 | Ayush      | Shukla    | 23500.00 |
|  6 | Harshita   | Singh     | 20000.00 |
|  5 | Akash      | Saini     |     NULL |
|  8 | Abhishek   | Mishra    |     NULL |
| 10 | Mahima     | Chaudhari |     NULL |
+----+------------+-----------+----------+
10 rows in set (0.00 sec)

Related Articles

Summary

So in this article, you have learned all about how to handle NULL values in MySQL database table with the help of the proper example. Here we have seen some possible ways to handle NULL values, You can go with any of them as per your requirement.

Handling of NULL values is one of the most especially when we are working with any real-life project when we have to deal with millions of records because without handling the missing values or Null values we can take better decisions on the data, that’s why handling missing values is needed.

If you found this article helpful, Please share and keep visiting for further MySQL tutorials.

Thanks for your valuable time… 🙏🙏❤️❤️

◀MySQL Update Statement With Examples

How to Extract Year from Date in MySQL▶