MySQL IS NULL Operator

Introduction

In this chapter, we will learn about the IS NULL operator in MySQL. The IS NULL operator is used to check for NULL values in a column. NULL represents a missing or undefined value in a database. The IS NULL operator is particularly useful for filtering records where specific columns have no value. We will cover the syntax, examples, and important considerations for using the IS NULL operator.

Syntax

The basic syntax for the IS NULL operator is:

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

• column_name: The column to check for NULL values.

To check for non-null values, you can use the IS NOT NULL operator:

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

Using IS NULL Operator

Example with SELECT

SELECT first_name, last_name, email
FROM students
WHERE email IS NULL;

This example retrieves rows where the email column has NULL values.

Example with IS NOT NULL

SELECT first_name, last_name, email
FROM students
WHERE email IS NOT NULL;

This example retrieves rows where the email column does not have NULL values.

Full Example

Let’s go through a full example where we create a table, insert data into it, and use the IS NULL operator to filter records.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create a Table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    enrollment_date DATE
);

4. Insert Data into the Table:

INSERT INTO students (first_name, last_name, email, enrollment_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', NULL, '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', NULL, '2023-07-04'),
('Sahil', 'Mehta', 'sahil.mehta@example.com', '2023-07-05');

5. Use the IS NULL Operator with SELECT:

SELECT first_name, last_name, email
FROM students
WHERE email IS NULL;

Output

6. Use the IS NOT NULL Operator with SELECT:

SELECT first_name, last_name, email
FROM students
WHERE email IS NOT NULL;

Output

Important Considerations

• NULL Values: NULL is not the same as an empty string or zero. It represents an unknown or missing value.
• Comparison with NULL: You cannot use regular comparison operators like = or <> to check for NULL values. Always use IS NULL or IS NOT NULL for such checks.
• Performance: Checking for NULL values can impact performance, especially on large datasets. Ensure appropriate indexing and optimize queries to improve performance.

Conclusion

The IS NULL operator is used for filtering data based on NULL values in MySQL queries. This chapter covered how to use the IS NULL and IS NOT NULL operators, provided examples with SELECT statements, and discussed important considerations.