Why equals NULL fails in SQL

Introduction

In SQL, comparing a value to NULL using equals (=) does not work because NULL means unknown or missing data. SQL treats NULL differently from normal values.

When you want to check if a database field has no value (is empty or missing).

When filtering records where a column might not have any data.

When you want to avoid wrong results by using proper NULL checks.

When writing queries that handle optional or incomplete information.

Syntax

SQL

SELECT * FROM table WHERE column IS NULL;
SELECT * FROM table WHERE column IS NOT NULL;

Use IS NULL to check if a value is NULL.

Using = NULL will never return true because NULL is not a value but a marker for missing data.

Examples

This finds employees who do not have a manager assigned.

SQL

SELECT * FROM employees WHERE manager_id IS NULL;

This finds orders that have been shipped (shipped_date has a value).

SQL

SELECT * FROM orders WHERE shipped_date IS NOT NULL;

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

SQL

SELECT * FROM products WHERE price = NULL;

Sample Program

This example shows how to find rows where the 'value' column is NULL using IS NULL. The second query tries = NULL and returns nothing.

SQL

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

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

-- Correct way to find NULL values
SELECT id FROM test_null WHERE value IS NULL;

-- Incorrect way that returns no rows
SELECT id FROM test_null WHERE value = NULL;

Important Notes

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

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

Summary

NULL is special and cannot be compared with = or !=.

Use IS NULL to find missing values.

Using = NULL always fails and returns no results.