MySQL Check if Column Is Null or Empty

In MySQL or any programming language, there is a privilege to add constraints or checks at the instance of table creation. These constraints help the user restrict the values inserted in a particular field of the table.

The restriction allows users to enter a well-defined set of data in the column. But, when these constraints are not applied at the schema creation, there is an option to extract the rows by the application of queries.

Check if Column Is Null or Empty in MySQL

The steps to filter out the null or empty column values present inside the table are given in the section ahead.

The syntax for NULL or Empty check is:

Select expression [, expression2] ...
     FROM table-name
    [WHERE column-name IS NULL or column-name = '']

In the query above, the basic syntax of Select gets used to form a statement that extracts null and empty values from the table. Explicitly some keywords like IS NULL get used along with column names to filter out null values.

And for the empty check, simple matching of column name with blank character is checked. This combination query of IS NULL keywords in the Select query extracts the subset rows having null or empty values in the column names.

Let’s understand the IS NULL check in detail:

The IS NULL keyword is an operator that checks null values over the columns. It is an in-between operator and gets used with other queries to perform actions like Select, Update, and Delete in MySQL.

List of queries before the actual checking query on tables:

  1. Create a table using the Create query in MySQL. The query will create the initial schema.

    Create table student ( id varchar(255), name varchar(255), dob date);
    

    In the above query, no constraints are used, such as primary key, unique, or not null. Hence, the schema extracts the desired results.

    The screenshot of the table created is added for reference.

    Table Schema With no Constraints

  2. Insert some values in a table.

    Insert into student values ( null,'Josheph', '2022-06-08');
    Insert into student values ( '117','', '2022-06-06');
    

    The above query inserts some desired values in the table. Other values can also get added.

Below is the screenshot for reference.

![List of Entries in the Table](/img/MySQL/mysql select all rows in table.JPG)
  1. Run the actual query to filter the null and empty rows.

    Query to execute the MySQL statement:

    Select * from student where name IS NULL or name = '';
    Select * from student where id IS NULL or id = '';
    

    The above query extracts the list of values present in the above table where the name is null or empty. Similarly, in the second Select query, a check on the ID field attribute gets matched with the empty character value or null value.

    Below are the actual local run screenshots for reference.

    Local Run Screenshot:

    MySQL Check if Column Value is Null

    MySQL Select if Column Value is Empty

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - MySQL Column

  • Update Multiple Columns in Multiple Rows With Different Values in MySQL
  • Use the ORDER BY Clause With Multiple Columns in MySQL
  • Change Column Datatype or Property in MySQL