Often database developers need to check if one or more columns in their MySQL table is empty or null. This is commonly required when you need to filter out rows with empty column values. 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.
Why check if Column is Empty or Null
There are many use cases where you need to check if a column is empty or null in MySQL. Here are some of the common ones:
- Check Data Integrity – It is important to identify columns with null or empty values and investigate if they are intentional or accidental. This will keep your data clean.
- Filter Data – Often database programmers and software developers need to run queries that exclude rows with empty or null values.
- Avoid errors – If you are using mathematical calculations in your SQL queries, then null and empty values can throw them off. So it is essential to identify and avoid such rows from your calculations, to get accurate results.
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. It contains 3 rows, one of which contains null value for amount column, and another row contains empty string ” for amount column.
mysql> create table sales(id int, amount varchar(5), order_date date);
mysql> describe sales;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| amount | varchar(5) | YES | | NULL | |
| order_date | date | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
mysql> insert into sales(id, amount, order_date) values(3, '', '2024-06-12');
mysql> insert into sales(id, amount, order_date) values(2, 50, '2024-06-13');
mysql> insert into sales(id, order_date) values(1, '2024-06-11');
mysql> select * from sales;
+------+--------+------------+
| id | amount | order_date |
+------+--------+------------+
| 3 | | 2024-06-12 |
| 2 | 50 | 2024-06-13 |
| 1 | NULL | 2024-06-11 |
+------+--------+------------+
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. There are 3 simple ways to check if your column is empty or contains null values.
1. Using Is Null
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 ”.
SELECT * FROM table_name WHERE column_name IS NULL;
Here is an example to illustrate it.
mysql> SELECT * FROM sales WHERE amount IS NULL;
+------+--------+------------+
| id | amount | order_date |
+------+--------+------------+
| 1 | NULL | 2024-06-11 |
+------+--------+------------+
You can also use this in combination with other operators such as equality operator. Here is an example to check if a column is null or empty.
SELECT * FROM table_name WHERE column_name IS NULL OR column_name = ''
Please note, you need to mention both the conditions separately and combine them using OR operator.
mysql> SELECT * FROM sales WHERE amount IS NULL OR amount = '';
+------+--------+------------+
| id | amount | order_date |
+------+--------+------------+
| 1 | NULL | 2024-06-11 |
+------+--------+------------+
| 3 | | 2024-06-12 |
+------+--------+------------+
If your column may contain whitespace characters then you can call trim function on the column before you check its value.
mysql> SELECT * FROM table_name WHERE column_name IS NULL OR trim(column_name) = '';
On the other hand, you can use IS NOT NULL to retrieve rows that do not have column with null values.
SELECT * FROM table_name WHERE column_name IS NOT NULL;
Here is a sample query.
mysql> SELECT * FROM sales WHERE amount IS NOT NULL;
+------+--------+------------+
| id | amount | order_date |
+------+--------+------------+
| 3 | | 2024-06-12 |
+------+--------+------------+
| 2 | 50 | 2024-06-13 |
+------+--------+------------+
2. Using Coalesce
COALESCE() function returns the first non-null value in a list of values. If there are no null values then it returns NULL. These values can be passed as literal strings or as column names. Here is an example to demonstrate it.
SELECT * FROM table_name WHERE COALESCE(column_name, '') = '';
Here is an example to demonstrate it.
mysql> SELECT * FROM sales WHERE COALESCE(amount, '') = '';
+------+--------+------------+
| id | amount | order_date |
+------+--------+------------+
| 3 | | 2024-06-12 |
| 1 | NULL | 2024-06-11 |
+------+--------+------------+
3. Using NULLIF
You can also use NULLIF() function for this purpose. It compares two expressions and returns null if they are equal. It can be used as shown below to retrieve rows where column is null or empty string.
SELECT * FROM table_name
WHERE NULLIF(column_name, ' ') IS NULL;
Here is an example to illustrate it.
mysql> SELECT * FROM sales
WHERE NULLIF(amount, ' ') IS NULL;
+------+--------+------------+
| id | amount | order_date |
+------+--------+------------+
| 3 | | 2024-06-12 |
| 1 | NULL | 2024-06-11 |
+------+--------+------------+
Conclusion
In this article, we have learnt how to check if column is empty or null. It is essential to know how to correctly check if a column is empty or null to obtain accurate results. Since MySQL offers several ways to check column values, it is very easy to make an error while checking for nulls and make wrong inferences. The key is to use IS NULL or COALESCE() function to check for nulls. Use equality and inequality operators only to check for empty strings. They don’t work on nulls. Depending on your requirement, you can use a combination of IS NULL, IS NOT NULL, equality and inequality operators to precisely obtain rows that meet specific conditions.
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
Related posts:
Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.