MySQL IS NOT NULL

The MySQL IS NOT NULL is used to test whether the user-given expression or column value is Not a NULL value or not. You can use this operator inside a Where clause to find the records with Not NULL values.

Let us see how to use this with an example, and the syntax of this IS NOT NULL to work with empty ones is as follows:

SELECT Column_Names  
FROM Tables_Name
WHERE Column_Value IS NOT NULL

MySQL IS NOT NULL Example

In this example, we show you simple examples of it so that you understand the functionality.

SELECT 0 IS NOT NULL;

SELECT 1 IS NOT NULL, NULL IS NOT NULL;

SELECT 1/0 IS NOT NULL, 0/1 IS NOT NULL;
Simple Example 1

For this demonstration, We are going to use the Employee Details table. The following screenshot shows you the data present inside this MySQL table

Customer Records 2

In this example, we use this operator to return all the Employees details whose Middle Name is Not NULL value.

SELECT 
       CustomerKey,
       FirstName, 
       MiddleName,
       LastName,
       YearlyIncome,
       Phone,
       Office,
       Mobile
 FROM EmployeeDetails
 WHERE MiddleName IS NOT NULL;
Example 3

The following query returns all the employee details records whose Office number is Not NULL.

SELECT 
       CustomerKey,
       FirstName, 
       MiddleName,
       LastName,
       YearlyIncome,
       Phone,
       Office,
       Mobile
 FROM EmployeeDetails
 WHERE Office IS NOT NULL;
Example 4

Until now, we are using this on a single column. In this example, we use this operator to return all the employee details whose Office Phone number and Mobile numbers are Not NULL values

SELECT 
       CustomerKey,
       FirstName, 
       MiddleName,
       LastName,
       YearlyIncome,
       Phone,
       Office,
       Mobile
 FROM EmployeeDetails
 WHERE Office IS NOT NULL AND Mobile IS NOT NULL;
MySQL IS NOT NULL Operator 5

Here, we are finding the employees whose Middle and Mobile number is Non Null values using this operator.

SELECT 
       CustomerKey,
       FirstName, 
       MiddleName,
       LastName,
       YearlyIncome,
       Phone,
       Office,
       Mobile
 FROM EmployeeDetails
 WHERE MiddleName IS NOT NULL OR Mobile IS NOT NULL;
Example 6