IS NULL and IS NOT NULL in MySQL

Introduction

We use IS NULL and IS NOT NULL to find out if a value is missing or present in a database column.

When you want to find records where a certain field has no value.

When you want to exclude records that have missing information.

When checking if optional data was provided or left empty.

When filtering results to only show entries with or without specific data.

Syntax

MySQL

SELECT column1, column2 FROM table_name WHERE column_name IS NULL;

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

Use IS NULL to find rows where the column has no value (NULL).

Use IS NOT NULL to find rows where the column has any value (not NULL).

Examples

Finds all employees who do not have a manager assigned.

MySQL

SELECT * FROM employees WHERE manager_id IS NULL;

Finds all customers who have provided an email address.

MySQL

SELECT name, email FROM customers WHERE email IS NOT NULL;

Finds orders that have not been shipped yet.

MySQL

SELECT order_id FROM orders WHERE shipped_date IS NULL;

Sample Program

This creates a table of students, some with a graduation year and some without. The query finds students missing their graduation year.

MySQL

CREATE TABLE students (
  id INT,
  name VARCHAR(50),
  graduation_year INT
);

INSERT INTO students VALUES
(1, 'Alice', 2023),
(2, 'Bob', NULL),
(3, 'Charlie', 2024);

SELECT name FROM students WHERE graduation_year IS NULL;

Important Notes

NULL means no value or unknown, not zero or empty string.

You cannot use = NULL or <> NULL; always use IS NULL or IS NOT NULL.

IS NULL and IS NOT NULL help handle missing data clearly.

Summary

Use IS NULL to find missing values in a column.

Use IS NOT NULL to find rows where the column has data.

These checks help manage and filter incomplete or optional data.