Why NULL is not a value in SQL

Introduction

NULL means 'unknown' or 'missing' data, not a real value. It shows that we don't know what the data is.

When a person's middle name is not provided in a database.

When a product's expiration date is not set yet.

When a survey answer is left blank by a respondent.

When a phone number is not available for a contact.

When a date of completion is not recorded for a task.

Syntax

SQL

SELECT column_name FROM table_name WHERE column_name IS NULL;

Use IS NULL to check for NULL values because NULL is not equal to anything, even itself.

NULL is different from zero or empty string; it means data is missing or unknown.

Examples

This finds all employees who do not have a middle name recorded.

SQL

SELECT * FROM employees WHERE middle_name IS NULL;

This finds products where the expiration date is not set.

SQL

SELECT * FROM products WHERE expiration_date IS NULL;

This finds survey responses where the answer was left blank.

SQL

SELECT * FROM survey_responses WHERE answer IS NULL;

Sample Program

This creates a table with some people, some have no middle name (NULL). Then it selects those people.

SQL

CREATE TABLE people (id INT, name VARCHAR(20), middle_name VARCHAR(20));
INSERT INTO people VALUES (1, 'Alice', NULL), (2, 'Bob', 'James'), (3, 'Carol', NULL);
SELECT id, name FROM people WHERE middle_name IS NULL;

Important Notes

NULL means unknown, so you cannot use = NULL to check it.

Use IS NULL or IS NOT NULL to test for NULL values.

NULL affects calculations and comparisons; results with NULL often become NULL.

Summary

NULL is not a value; it means data is missing or unknown.

Use IS NULL to find NULLs, not = NULL.

NULL behaves differently in comparisons and calculations.