IS NULL vs equals NULL in SQL

Introduction

We use IS NULL to check if a value is missing or empty in a database. Using = NULL does not work because NULL means unknown, not a value.

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

When filtering data to exclude or include rows with missing information.

When checking if a user did not provide input in a form stored in the database.

Syntax

SQL

SELECT column_name FROM table_name WHERE column_name IS NULL;

SELECT column_name FROM table_name WHERE column_name = NULL;

IS NULL is the correct way to check for missing values.

= NULL will not work as expected because NULL is not equal to anything, even NULL itself.

Examples

This finds all employees who do not have a manager assigned.

SQL

SELECT * FROM employees WHERE manager_id IS NULL;

This query will return no rows because = NULL does not work for checking NULL.

SQL

SELECT * FROM employees WHERE manager_id = NULL;

This finds all orders that have not been shipped yet.

SQL

SELECT * FROM orders WHERE shipped_date IS NULL;

Sample Program

This creates a table with some values and NULLs, then tries to find rows where value is NULL using both methods.

SQL

CREATE TABLE test_null (
  id INT,
  value VARCHAR(10)
);

INSERT INTO test_null (id, value) VALUES
(1, 'apple'),
(2, NULL),
(3, 'banana');

SELECT id FROM test_null WHERE value IS NULL;
SELECT id FROM test_null WHERE value = NULL;

Important Notes

NULL means unknown or missing data, not zero or empty string.

Always use IS NULL or IS NOT NULL to check for NULL values.

Summary

IS NULL checks for missing values correctly.

= NULL does not work and returns no results.

Use IS NULL in your queries to handle NULLs properly.