The MySQL IS NULL is used to test whether the user given expression or column value is NULL or not. You can use this function inside a Where clause to find the records with NULL values.
In this article, we will show you, How to use the IS NULL with example
MySQL IS NULL Syntax
The basic syntax behind this MySQL IS NULL is as follows:
SELECT Column_Names
FROM Table_Name
WHERE Column_Value IS NULL
Simple MySQL IS NULL Example 1
In this example, we will show you the simple examples of IS NULL operator. Here, 1 is Not a Null value, 0 or NULL are Null Values. In the last statement, 1/0 is a NULL.
SELECT 1 IS NULL;
SELECT 0 IS NULL, NULL IS NULL;
SELECT 1/0 IS NULL, 0/1 IS NULL;
OUTPUT
MySQL IS NULL Example 2
For this IS NULL demonstration, We are going to use the Employee Details table. The following screenshot shows you the data inside this table
In this example, we use this IS NULL to return all the Employees details whose Middle Name is NULL value
SELECT
CustomerKey,
FirstName,
MiddleName,
LastName,
YearlyIncome,
Phone,
Office,
Mobile
FROM `MySQL Tutorial`.EmployeeDetails
WHERE MiddleName IS NULL;
OUTPUT
The following Query returns all the employee records whose Phone number is NULL.
SELECT
CustomerKey,
FirstName,
MiddleName,
LastName,
YearlyIncome,
Phone,
Office,
Mobile
FROM `MySQL Tutorial`.EmployeeDetails
WHERE Phone IS NULL;
OUTPUT
Until now, we are using this IS NULL on a single column. In this example, we use this operator to return all the employee details, whose Personal Phone number or Office numbers are NULL values
SELECT
CustomerKey,
FirstName,
MiddleName,
LastName,
YearlyIncome,
Phone,
Office,
Mobile
FROM `MySQL Tutorial`.EmployeeDetails
WHERE Phone IS NULL OR Office IS NULL;
OUTPUT
Here, we are finding the employees whose Middle is Null and their Phone number is also Null.
SELECT
CustomerKey,
FirstName,
MiddleName,
LastName,
YearlyIncome,
Phone,
Office,
Mobile
FROM `MySQL Tutorial`.EmployeeDetails
WHERE MiddleName IS NULL AND Phone IS NULL;
OUTPUT
Thank You for Visiting Our Blog
