MySQL NULL Values – IS NULL and IS NOT NULL

In MySQL while inserting records sometimes a few records are left empty, these empty values are called MySQL NULL values. To test the presence of these values the “IS NULL” and “IS NOT NULL” operators are available.

This post will discuss:

The “IS NULL” Operator in MySQL
The “IS NULL” Operator With “AND” Operator in MySQL
The “IS NULL” Operator With “OR” Operator in MySQL
The “IS NOT NULL” Operator in MySQL
The “IS NOT NULL” Operator With “AND” Operator in MySQL
The “IS NOT NULL” Operator With “OR” Operator in MySQL
Complex Conditions With “IS NULL” and “IS NOT NULL” Operators

Prerequisite: Display Data of the Table

Before starting this post, make sure to log in to the MySQL server and select the database. Once the database is changed, use this syntax to see the table data available in the database:

SELECT * FROM <table-name>;

The table name for this post is “employee”:

SELECT * FROM employee;

The output is displaying records of the table “employee” and it is visible that few records contain “NULL” value:

Note: The NULL means that this record that was left empty at the time of creation. However, this does not mean zero or a record containing space.

Let’s learn about the “IS NULL” and “NOT NULL” operators in MySQL.

“IS NULL” Operator in MySQL

In MySQL, the “IS NULL” operator returns true if the value is NULL; otherwise, it returns false. When this operator is used in the “SELECT” statement containing a “WHERE” clause, it displays all the NULL values in the table that match a condition. The syntax for it is given below:

SELECT * FROM [table-name] WHERE [column-name] IS NULL;

Let’s see an example, to display all the records from the table “employee” where the “phone” column value “IS NULL”, by running this command:

SELECT * FROM employee WHERE Phone IS NULL;

The output displays the records where the value of “phone” is empty:

You have successfully extracted the NULL records based on a condition. The “WHERE” clause can contain multiple conditions using logical “or” and “and” operators.

“IS NULL” Operator With “AND” Operator in MySQL

The “WHERE” clause can contain multiple conditions having the “IS NULL” operator with the “AND” operator in between them, to display the records if both conditions are true. The syntax is given below:

SELECT * FROM [table-name] WHERE [column-name] IS NULL AND [column-name] IS NULL;

If a user wants to display the data from the “employee” table when the “Phone” and “Country” values are NULL by running this command:

SELECT * FROM employee WHERE Phone IS NULL AND Country IS NULL;

The output displays the record that fulfills multiple conditions have “IS NULL” operator:

“IS NULL” Operator With “OR” Operator in MySQL

The “WHERE” clause has the ability to provide multiple conditions having the “IS NULL” operator with the “OR” operator in between them, to display the records if any condition is true. The syntax is given below:

SELECT * FROM [table-name] WHERE [column-name] IS NULL OR [column2-name] IS NULL;

Let’s see an example, to display the records from the “employee” table when either the value of “Phone” or “Country” is empty or even when both are empty, by running the command:

SELECT * FROM employee WHERE Phone IS NULL OR Country IS NULL;

The output is showing records obtained from the query:

“IS NOT NULL” Operator in MySQL

The “IS NOT NULL” operator in MySQL returns true if the value is not empty otherwise it returns false. When the “IS NOT NULL” operator is used in the “SELECT” statement in the “WHERE” clause with the condition to filter data where the values are not empty. The syntax is given below:

SELECT * FROM [table-name] WHERE [column-name] IS NOT NULL;

For example, the user wants to filter data from the “employee” table where the “Phone” value is not empty. Run this command:

SELECT * FROM employee WHERE Phone IS NOT NULL;

The output is displaying the non-empty values in “Phone” retrieved using the query:

“IS NOT NULL” Operator With “AND” Operator in MySQL

The “WHERE” clause can contain multiple conditions having the “IS NOT NULL” operator with the “AND” operator in between them, to display the non-empty records if both conditions are true. The syntax is given below:

SELECT * FROM [table-name] WHERE [column-name] IS NOT NULL AND [column-name] IS NOT NULL;

For example, if the user wants to filter the records of the “employee” table, when “Phone” and “Country” both have non-empty values, type this command:

SELECT * FROM employee WHERE Phone IS NOT NULL AND Country IS NOT NULL;

The output is displaying the filtered records that fulfill the conditions:

“IS NOT NULL” Operator With “OR” Operator in MySQL

The “WHERE” clause has the ability to provide multiple conditions having the “IS NOT NULL” operator with the “OR” operator in between them, to display the non-empty records if any condition is true. The syntax is given below:

SELECT * FROM [table-name] WHERE [column-name] IS NOT NULL OR [column2-name] IS NOT NULL;

For example, to display the records from the “employee” table when either the value of “Phone” or “Country” is non-empty or even when both are non-empty, by running the command:

SELECT * FROM employee WHERE Country IS NOT NULL OR Phone IS NOT NULL;

The output displays the output having non-empty values in “Phone” and “Country”:

Complex Conditions With “IS NULL” and “IS NOT NULL” Operators

The user can use both “IS NULL” and “IS NOT NULL” operators to form complex conditions by using logical “AND” and “OR” operators in between them.

Let’s see an example to display the records from the table “employee” where the value of “Country” is non-empty and the value of “Phone” is NULL, run this query:

SELECT * FROM employee WHERE Country IS NOT NULL AND Phone IS NULL;

The query is displaying the records that match the query:

Let’s see another example if the user wants to extract records from the “employee” table. when either the value of “Country” is non-empty or “Phone” is empty or even when both conditions are fulfilled, by executing this query:

SELECT * FROM employee WHERE Country IS NOT NULL OR Phone IS NULL;

The output displays the results obtained by the query:

This post has explained “IS NULL” and “IS NOT NULL” operators with simple and multiple conditions along with their examples.

Conclusion

The “IS NULL” operator tests for the non-empty values, whereas the “IS NOT NULL” operator tests for the empty values. When these operators are used in the “SELECT” statement with a “WHERE” clause it filters the results depending on the condition. This post demonstrated the usage of “IS NULL” and “IS NOT NULL” operators in MySQL.