How to Compare Null Values in MySQL?

You may sometimes need to compare a column with a null value or compare two columns where one of them has null values, or even perform null checks on columns where one of them has null values. This article is going to take a look at how to compare null values in MySQL. In MySQL, you can use it to compare null values.

Example:

As an example, let’s say you have the following table orders(id, order_date, cost_price, selling_price):

1. Create Table

#start
create table sales(
                id int, 
                order_date date, 
                cost_price int, 
                selling_price int);
#end

2.Describe orders;

#start
Describe orders;
#end

3. Insert data 

#start
insert into orders(id,order_date,cost_price,selling_price)
       values(1,'2020-11-01',150,250),
             (2,'2020-11-02',200,300);
#end
#start
 insert into orders(id,order_date, cost_price) 
       values(3, '2020-11-03',100);
#end
#start
 insert into orders(id,order_date, selling_price) 
       values(4, '2020-11-03',100);
#end

4. Select table 

#start
select * from orders;
#end

As an example, let us say that you would like to select rows where the cost price is null. It is typical for you to use an expression such as cost_price=null, but this expression does not work when the value is NULL. There are times when you need to use the IS operator in case of null comparisons, that is, in the case of cost_price IS null.

The following SQL query will not work as a result

#start
select * from orders where cost_price=null;
Empty set (0.00 sec)
#end

In order to make this work, you will need to run the following SQL query. To select rows where cost_price is null, we need to use the following SQL query.

#start
select * from orders where cost_price is null;
#end

In the same way, here is the SQL query we can use to select rows in which cost_price is not NULL. Using the IS NOT operator, we are able to achieve the desired result.

#start
select * from orders where cost_price is not null;
#end