How to Compare Null Values in MySQL

Last updated on August 28th, 2024 at 05:10 am

Sometimes you may need to compare a column with null value or compare two columns where one of them has null values, or even perform null check for columns. Null values mean missing or unknown values and are often present in real world data. They behave differently compared to similar values such as empty strings and dates. They can often trip beginner database developers who assume them to work the same way as strings and dates, while comparing values. In this article we will look at how to compare null values in MySQL. You can use it for Null comparison in MySQL.

Understanding NULL values in MySQL

In MySQL, and in most other databases, null values react very differently from strings and other data types while performing comparisons. For example, if you check the condition null=null, it will not return True or False but return Null instead. If you compare null with any non-null value, such as null=’abc’ the result will again be null and not True/False. This makes it difficult to filter rows based on columns which can have null values. For this purpose, you need to use is null and is not null operators respectively.

How to Compare Null Values in MySQL

Let us say you have the following table sales(id, order_date, cost_price, selling_price)

mysql> create table sales(
                id int, 
                order_date date, 
                cost_price int, 
                selling_price int);

mysql> describe sales;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| id            | int(11) | YES  |     | NULL    |       |
| order_date    | date    | YES  |     | NULL    |       |
| cost_price    | int(11) | YES  |     | NULL    |       |
| selling_price | int(11) | YES  |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+

mysql> insert into sales(id,order_date,cost_price,selling_price)
       values(1,'2020-11-01',150,250),
             (2,'2020-11-02',200,300);

mysql> insert into sales(id,order_date, cost_price) 
       values(3, '2020-11-03',100);

mysql> insert into sales(id,order_date, selling_price) 
       values(4, '2020-11-03',100);

mysql> select * from sales;
+------+------------+------------+---------------+
| id   | order_date | cost_price | selling_price |
+------+------------+------------+---------------+
|    1 | 2020-11-01 |        150 |           250 |
|    2 | 2020-11-02 |        200 |           300 |
|    3 | 2020-11-03 |        100 |          NULL |
|    4 | 2020-11-03 |       NULL |           100 |
+------+------------+------------+---------------+

Also read : How to Compare Two Rows from Same Table

Let us say you want to select rows where cost price is null. Typically, you would use an expression such as cost_price=null but it does not work with NULL values. In case of null comparison, you need to use IS operator, that is, cost_price IS null

So the following SQL query will not work

mysql> select * from sales where cost_price=null;
Empty set (0.00 sec)

Using Is Null

To filter rows that contain column value to be null, you need to use is null operator. Here is its syntax.

column_name is null

The following SQL query will work. Here is the SQL query to select rows where cost_price is null.

mysql> select * from sales where cost_price is null;
+------+------------+------------+---------------+
| id   | order_date | cost_price | selling_price |
+------+------------+------------+---------------+
|    4 | 2020-11-03 |       NULL |           100 |
+------+------------+------------+---------------+

Also Read : How to Compare Two Columns in MySQL

Using Is Not Null

On the other hand, if you want to filter rows where column value is not null, then use is not null operator.

Similarly, here is the SQL query to select rows where cost_price is not NULL. In this case, we use IS NOT operator.

mysql> select * from sales where cost_price is not null;
+------+------------+------------+---------------+
| id   | order_date | cost_price | selling_price |
+------+------------+------------+---------------+
|    1 | 2020-11-01 |        150 |           250 |
|    2 | 2020-11-02 |        200 |           300 |
|    3 | 2020-11-03 |        100 |          NULL |
+------+------------+------------+---------------+

You can use IS NULL and IS NOT NULL with UPDATE, INSERT and DELETE statement. Here are some examples to illustrate it.

With UPDATE Statement

mysql> update sales
       set selling_price=350
       where cost_price is null;

mysql> select * from sales;
+------+------------+------------+---------------+
| id   | order_date | cost_price | selling_price |
+------+------------+------------+---------------+
|    1 | 2020-11-01 |        150 |           250 |
|    2 | 2020-11-02 |        200 |           300 |
|    3 | 2020-11-03 |        100 |          NULL |
|    4 | 2020-11-03 |       NULL |           350 |
+------+------------+------------+---------------+

Also Read : How to Get Every Nth Row in MySQL

With INSERT Statement

Here’s an example that uses MySQL NULL Comparison in INSERT statement.

mysql> insert into new_sales(id,order_date, cost_price, selling_price)
      select * from sales
      where cost_price is null;

Also read : How to Add Auto Increment Column

With DELETE Statement

Here’s an example to use Null comparison in Delete Statement.

mysql> delete from sales
       where cost_price is null;

mysql> select * from sales;
+------+------------+------------+---------------+
| id   | order_date | cost_price | selling_price |
+------+------------+------------+---------------+
|    1 | 2020-11-01 |        150 |           250 |
|    2 | 2020-11-02 |        200 |           300 |
|    3 | 2020-11-03 |        100 |          NULL |
+------+------------+------------+---------------+

Conclusion

Most real-world databases have columns with null values. They behave quite differently compared to other columns when it comes to comparison. Therefore, if you use the traditional comparison operators, you may not get desired result. You need to use is null and is not null operators for this purpose. Hopefully, this article will help you compare NULL values in MySQL.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.