check if column is empty or null in mysql

How to Check if Column is Empty or Null in MySQL

Often database developers need to check if one or more columns in their MySQL table is empty or null. People end up using comparison operator such as = and <> to check these things which does not work out fully. 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

Typically, here is the MySQL query developers use to check if column is empty or null in MySQL.

mysql> SELECT * FROM table_name WHERE column_name = NULL OR column_name = '';

You can use comparison operator = to check if column is empty ” but you cannot use it to check if it is null. In this case, even if the column is null MySQL will always return false. For example, let us say you have the following MySQL table.

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

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

mysql> insert into sales(id, amount) values(1, 30);

mysql> select * from sales;
+------+--------+------------+
| id   | amount | order_date |
+------+--------+------------+
|    1 |     30 | NULL       |
+------+--------+------------+

If you use = operator to check if a column is null or empty, here is the result you get.

mysql> select * from sales where order_date=null;
Empty set (0.01 sec)

mysql> select * from sales where order_date=null or order_date='';
Empty set, 2 warnings (0.01 sec)

mysql> select * from sales where order_date='';
Empty set, 2 warnings (0.00 sec)

As you can see, no matter how you try to use = operator, it just doesn’t work properly.

To check if a column is null or not, you need to use IS operator. Null or Not Null vales can be checked only using IS or IS NOT operator. The comparison operator ” is used for comparison with string values, such as empty string ”.

mysql> SELECT * FROM table_name WHERE column_name IS NULL OR column_name = '';
+------+--------+------------+
| id   | amount | order_date |
+------+--------+------------+
|    1 |     30 | NULL       |
+------+--------+------------+

In this article, we have learnt how to check if column is empty or null.

Also read:

How to Modify MySQL Column to Allow Null
How to Schedule Multiple Cron Jobs in One Crontab
How to POST JSON Data in Python
How to Remove SSL Certificate & SSH Passphrase
How to Capture Linux Signal in Python

Leave a Reply

Your email address will not be published.