WHERE with IS NULL and IS NOT NULL in SQL

Introduction

We use WHERE with IS NULL or IS NOT NULL to find rows where a column has no value or does have a value. This helps us filter data based on missing or existing information.

Finding customers who have not provided their phone number.

Listing orders that do not have a delivery date set yet.

Checking which employees have not been assigned a manager.

Filtering products that have no discount applied.

Identifying records where optional information is missing.

Syntax

SQL

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 check if a column has no value (NULL).

Use IS NOT NULL to check if a column has any value (not NULL).

Examples

Find employees who do not have a manager assigned.

SQL

SELECT name FROM employees WHERE manager_id IS NULL;

Find orders that already have a delivery date.

SQL

SELECT order_id FROM orders WHERE delivery_date IS NOT NULL;

Find products without any discount.

SQL

SELECT product_name FROM products WHERE discount IS NULL;

Sample Program

This example creates a customers table with some phone numbers missing. Then it shows how to find customers with and without phone numbers.

SQL

CREATE TABLE customers (
  id INT,
  name VARCHAR(50),
  phone VARCHAR(20)
);

INSERT INTO customers (id, name, phone) VALUES
(1, 'Alice', '123-4567'),
(2, 'Bob', NULL),
(3, 'Charlie', '987-6543'),
(4, 'Diana', NULL);

-- Find customers without phone number
SELECT name FROM customers WHERE phone IS NULL;

-- Find customers with phone number
SELECT name FROM customers WHERE phone IS NOT 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 are very useful to handle missing data.

Summary

Use WHERE column IS NULL to find rows with missing values.

Use WHERE column IS NOT NULL to find rows with existing values.

NULL is special and needs IS NULL or IS NOT NULL to check.