SQL IS NULL Operator

Introduction

In this chapter, we will learn how to use the IS NULL operator in SQL. The IS NULL operator is used to test for empty values (NULL values) in a database. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the IS NULL operator effectively.

What is the IS NULL Operator?

The IS NULL operator is used to filter records that contain NULL values in a specified column. NULL values represent missing or undefined data. It is important to note that NULL is different from an empty string or a zero value; it specifically denotes the absence of a value.

Syntax for IS NULL

Basic Syntax

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

• column1, column2, ...: The columns you want to retrieve.
• table_name: The name of the table from which you want to retrieve data.
• column_name: The column to be tested for NULL values.

Example

Assume we have a table named employees:

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

This command retrieves the first_name, last_name, and email columns from the employees table where the email is NULL.

Step-by-Step Example

1. Create Sample Tables

First, we will create two sample tables named employees and departments.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', NULL, 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', NULL, 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

3. Using the IS NULL Operator

To retrieve employees whose email is NULL:

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

Output

first_name	last_name	email
Sita	Patel	NULL
Priya	Sharma	NULL

4. Using IS NOT NULL

The IS NOT NULL operator is used to filter records that do not contain NULL values in a specified column.

Example

To retrieve employees whose email is not NULL:

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

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Arjun	Singh	arjun.singh@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

Using IS NULL with Other Operators

The IS NULL operator can be combined with other SQL operators to create more complex queries.

Example with AND

To retrieve employees in department 2 whose email is NULL:

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 2 AND email IS NULL;

Output

first_name	last_name	email
Sita	Patel	NULL
Priya	Sharma	NULL

Example with OR

To retrieve employees in department 1 or employees whose email is NULL:

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1 OR email IS NULL;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Arjun	Singh	arjun.singh@example.com
Sita	Patel	NULL
Priya	Sharma	NULL

Conclusion

The IS NULL operator is a fundamental tool for filtering records with NULL values in SQL queries. This chapter covered the basic syntax, using IS NULL and IS NOT NULL to filter records, and combining IS NULL with other SQL operators. Understanding how to use the IS NULL operator effectively will enhance your ability to query and analyze your database data.