Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How do I check if a column is empty or null in MySQL?
To check if a column is empty or null , we can use the WHERE clause with IS NULL and for empty we can use the condition ' ' i.e., empty space. The steps required for this are as folllows: First a table is created with the help of CREATE command as follows −
mysql> CREATE table ColumnValueNullDemo -> ( -> ColumnName varchar(100) -> ); Query OK, 0 rows affected (0.61 sec)
An empty value is inserted into the table using INSERT command. This is given below −
mysql> INSERT into ColumnValueNullDemo values(' ');
Query OK, 1 row affected (0.14 sec)After that, the table records are displayed using the SELECT command. This is given as follows −
mysql> SELECT * from ColumnValueNullDemo;
After executing the above query, we will get the following output −
+-------------------+ | ColumnName | +-------------------+ | | +-------------------+ 1 row in set (0.00 sec)
To check if the column has null value or empty, the syntax is as follows −
SELECT * FROM yourTableName WHERE yourSpecificColumnName IS NULL OR yourSpecificColumnName = ' ';
The IS NULL constraint can be used whenever the column is empty and the symbol (' ') is used when there is empty value.
Now, the query using the above syntax is given as follows −
mysql> SELECT * FROM ColumnValueNullDemo WHERE ColumnName IS NULL OR ColumnName = ' ';
After executing the above query, the output obtained is.
+------------+ | ColumnName | +------------+ | | +------------+ 1 row in set (0.00 sec)
This output was obtained as the second condition is true for empty value.
Now, NULL value is inserted into the table with the help of the insert command as follows −
mysql> INSERT into ColumnValueNullDemo values(); Query OK, 1 row affected (0.14 sec)
The select command is used to view the contents of the table as follows −
mysql> SELECT * from ColumnValueNullDemo;
After executing the above query, the following output is obtained −
+------------+ | ColumnName | +------------+ | | | NULL | +------------+ 2 rows in set (0.00 sec)
Now, the condition to check for null or empty is applied −
mysql> SELECT * FROM ColumnValueNullDemo WHERE ColumnName IS NULL OR ColumnName = '';
Both the table rows are obtained as output because it is true in both conditions.
+------------+ | ColumnName | +------------+ | | | NULL | +------------+ 2 rows in set (0.00 sec)
43K+ Views
