In this tutorial, we will learn what NULL value is and how to work with NULL values in the MySQL database. We will also see some useful functions that MySQL provides to deal with NULL values. So, let’s get started!
Introduction to MySQL NULL
There are misconceptions between NULL and 0 in beginners.
In MySQL, NULL denotes the unknown value. Whereas, 0 denotes some value is there. For example, the number of students in class A is 0. However, if you don’t know the value of students in class B, then it is NULL because it is unknown.
Another important point is that the NULL value is not equal to any other value, not even itself. If you compare the NULL value to itself in MySQL, you will get the result false.
Let’s have a look at the below query and result-
SELECT IF(NULL=NULL,'True','False');Code language: SQL (Structured Query Language) (sql)
As you can see, the query returns false as both are not equal.
This point is important as we will need it further in this tutorial.
How to Specify Null Value in MySQL
The NULL simply means the value is missing, unknown or not applicable. So, if you skip any value while inserting data into the table, it will become a null value.
To specify whether the column supports null values or not, you can use the NOT NULL constraint as shown below.
CREATE TABLE NullDemo(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT,
city VARCHAR(50)
);Code language: SQL (Structured Query Language) (sql)Here, we have created a column ‘name’ that does not accept the NULL values, but the column ‘age’ and ‘city’ does.
Inserting NULL Values
To insert a NULL value in the table, you can either skip that column name while inserting a value or you can use the NULL keyword to specify the null value.
Let’s try inserting null values using both methods.
First, we will try to insert the value only in the name and city columns. Therefore, the age column will get the null value.
INSERT INTO NullDemo(name,city)
VALUES ("Raj","Mumbai");Code language: SQL (Structured Query Language) (sql)Now let’s insert the null value using the NULL keyword.
INSERT INTO NullDemo(name,age,city)
VALUES("Sham",NULL,"Delhi");Code language: SQL (Structured Query Language) (sql)Both values are inserted successfully. Let’s check the table to see what type of data we get.
SELECT * FROM NullDemo;Code language: SQL (Structured Query Language) (sql)
As you can see, both rows are holding null values for the age column.
MySQL ORDER BY with NULL
If you fetch the data using the ORDER BY clause, the query will return the rows which are having NULL values on the top. Because the null value is considered as lowest among others.
SELECT * FROM NullDemo ORDER BY age;Code language: SQL (Structured Query Language) (sql)
If you sort the records using the ORDER BY DESC clause, the records containing null values will appear at the bottom.
Find Only NULL/Not NULL Records
To find the records which are set to null, you can use the IS NULL operator with the WHERE clause.
Note that, you can not compare the records using the = operator with the WHERE clause as NULL is not equal to anything.
SELECT * FROM NullDemo WHERE age is NULL;Code language: SQL (Structured Query Language) (sql)
To find the records which are not null, you can use the IS NOT NULL operator.
SELECT * FROM NullDemo WHERE age is NOT NULL;Code language: SQL (Structured Query Language) (sql)
MySQL NULL Functions
There is a number of functions that mysql provides us to deal with the NULL values. Some of them are IFNULL, ISNULL, NULLIF, COALESCE etc.
Let’s see them one by one.
IFNULL Function
The IFNULL function accepts two parameters. If the first parameter is null, the function returns the second parameter. Else, it will return the first parameter.
Let’s take an example-
Here, we will display ‘N/A’ if the value is NULL using the IFNULL function.
SELECT id,name,IFNULL(age,'N/A')AS age,city FROM NullDemo;Code language: SQL (Structured Query Language) (sql)
As you can see, we have received the expected output.
ISNULL Function
The ISNULL function returns 1 if the parameter passed to it evaluates to NULL. Else, it will return 0.
SELECT ISNULL(NULL);Code language: SQL (Structured Query Language) (sql)
As you can see, we get 1 as a result.
Using the ISNULL function with the IF statement, we can perform the same operation as the previous.
We will display ‘N/A’ wherever the values are NULL.
SELECT id,name,IF(ISNULL(age)=1,'N/A',age) AS age,city FROM NullDemo;Code language: SQL (Structured Query Language) (sql)
COALESCE Function
The COALESCE function accepts an array of arguments and returns the first non-null argument from that array.
We can use the COALESCE function to perform the same operation as the previous.
If we want to display a default value if the column value is null then we can set the default value as the last parameter to the COALESCE function.
Let’s take an example and understand it clearly.
SELECT id,name,COALESCE(age,'N/A')AS age ,city from nulldemo;Code language: SQL (Structured Query Language) (sql)Here, we have passed the last parameter ‘N/A’ so that, if we get the age value as null, it will skip the first parameter and return the second parameter as it is a non-null value.
Summary
In this tutorial, we learned-
- What is a NULL value
- Difference between NULL and 0
- How to Specify a NULL value
- How to insert a NULL value in the table
- ORDER BY clause with the NULL
- Find NULL/non-NULL values
- MySQL NULL Functions