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.