How to Check if Column is Empty or Null in MySQL

Often database developers need to check if a column is empty or null in MySQL table. This is because many times the data that you receive is not clean and contains nulls and empty strings in one or more columns. So such checks are required mostly in SELECT statements in case you want to avoid picking rows where one or more columns are null or empty. But this can be tricky because typically people use equality operator (=) to check a column for nulls, and inequality operator (<>) to check if they are not null. But if you use equality/inequality operator to check for null values, it won’t work. You need to go about it another way. In this article, we will learn how to check if column is empty or null in MySQL.

How to Check if Column is Empty or Null in MySQL

Let us say you have the following MySQL table sales(id, order_date, product, amount).

mysql> create table sales(id int, order_date date, product varchar(10), amount int);

mysql> insert into sales(id, order_date, product, amount) 
values(1, '2022-10-01','', 100),(2, '2022-10-02','B', null),(3,'2022-10-03',null,300);

mysql> select * from sales;
+------+------------+---------+--------+
| id   | order_date | product | amount |
+------+------------+---------+--------+
|    1 | 2022-10-01 |         |    100 |
|    2 | 2022-10-02 | B       |   NULL |
|    3 | 2022-10-03 | NULL    |    300 |
+------+------------+---------+--------+

In the above table, product column has empty string as well as null value.

Here is the query to select rows where product column is empty or null.

mysql> select * from sales where product is null or product='';
+------+------------+---------+--------+
| id   | order_date | product | amount |
+------+------------+---------+--------+
|    1 | 2022-10-01 |         |    100 |
|    3 | 2022-10-03 | NULL    |    300 |
+------+------------+---------+--------+

Please note, when you check a column for null value you need to use IS operator and not equality operator (=). But when you check a column for empty string, you need to use equality operator and not IS operator.

Here is the query to select rows from sales table where product is not null and not empty.

mysql> select * from sales where product is not null and product<>'';
+------+------------+---------+--------+
| id   | order_date | product | amount |
+------+------------+---------+--------+
|    2 | 2022-10-02 | B       |   NULL |
+------+------------+---------+--------+

Please note, when you check a column for not null value you need to use IS NOT operator and not inequality operator (<>). But when you check a column for empty string, you need to use inequality operator and not IS NOT operator.

In this article, we have learnt how to check if column is empty or null in MySQL. This is very useful if you want to avoid null or empty columns in your select query result. In fact, such checks are commonly used for doing data sanitization before processing them.

Of course, these things are not required if you already have provided column constraints that prevent nulls and empty strings.

How to Check if Column is Empty or Null in MySQL

How to Check if Column is Empty or Null in MySQL

Related posts:

Leave a Reply Cancel reply