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:
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
Delete in MySQL.
List of queries before the actual checking query on tables:
Create a table using the
Createquery 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
not null. Hence, the schema extracts the desired results.
The screenshot of the table created is added for reference.
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)
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
Selectquery, 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: