How to Use the IS NULL and IS NOT NULL Operators in MySQL

08/04/2021

Contents

In this article, you will learn how to use the IS NULL and IS NOT NULL operators in MySQL.

Using the IS NULL and IS NOT NULL operators in MySQL

MySQL provides two operators to check for NULL values: IS NULL and IS NOT NULL. These operators are used in SQL queries to filter records based on whether a column contains NULL values or not.

IS NULL operator

The IS NULL operator is used to filter records where a column contains NULL values. Here’s the syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

For example, let’s say we have a table called “employees” with columns “id”, “name”, and “salary”. If we want to find all the employees who do not have a salary, we can use the following query:

SELECT name
FROM employees
WHERE salary IS NULL;

This query will return all the names of employees who do not have a salary.

IS NOT NULL operator

The IS NOT NULL operator is used to filter records where a column does not contain NULL values. Here’s the syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

For example, let’s say we have a table called “customers” with columns “id”, “name”, and “email”. If we want to find all the customers who have an email address, we can use the following query:

SELECT name
FROM customers
WHERE email IS NOT NULL;

This query will return all the names of customers who have an email address.

Using IS NULL and IS NOT NULL with other operators

IS NULL and IS NOT NULL can also be used in combination with other operators, such as =, <>, >, <, >=, and <=. Here are some examples:

SELECT name
FROM employees
WHERE salary IS NULL OR salary < 50000;

This query will return all the names of employees who either do not have a salary or have a salary less than 50000.

SELECT name
FROM customers
WHERE email IS NOT NULL AND name LIKE 'J%';

This query will return all the names of customers who have an email address and whose name starts with the letter "J".

#MySQL