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 the IS NOT NULL with example. And the syntax of this MySQL IS NOT NULL to work with Null values is as follows:

SELECT Column_Names  
FROM Tables_Name
WHERE Column_Value IS NOT NULL

Simple MySQL IS NOT NULL Example 1

In this example, we show you the simple examples of IS NOT NULL operator.

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;
MySQL IS NOT NULL Operator 1

IS NOT NULL Example 2

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

MySQL IS NOT NULL Operator 2

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

SELECT 
    CustomerKey,
    FirstName, 
    MiddleName,
    LastName,
    YearlyIncome,
    Phone,
    Office,
    Mobile
FROM `MySQL Tutorial`.EmployeeDetails
WHERE MiddleName IS NOT NULL;
MySQL IS NOT NULL Operator 3

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

SELECT 
    CustomerKey,
    FirstName, 
    MiddleName,
    LastName,
    YearlyIncome,
    Phone,
    Office,
    Mobile
FROM `MySQL Tutorial`.EmployeeDetails
WHERE Office IS NOT NULL;
MySQL IS NOT NULL Operator 4

Until now, we are using this IS NOT NULL on a single column. In this example, we use this is not null 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 `MySQL Tutorial`.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 is Not Null, and their Mobile number is also a Non-Null value using MySQL Is not Null.

SELECT 
    CustomerKey,
    FirstName, 
    MiddleName,
    LastName,
    YearlyIncome,
    Phone,
    Office,
    Mobile
FROM `MySQL Tutorial`.EmployeeDetails
WHERE MiddleName IS NOT NULL OR Mobile IS NOT NULL;
MySQL IS NOT NULL Operator 6