mysql null comparison

How to Compare Null Values in MySQL

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. In this article we will look at how to compare null values in MySQL. You can use it for Null comparison in MySQL.


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)

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

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.


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 |
+------+------------+------------+---------------+

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.

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!