Back to course

Handling NULL Values and IS NULL

Database Fundamentals: From Zero to Hero

Lesson 23: Handling NULL Values and IS NULL

Understanding NULL

NULL is not the same as zero (0) or an empty string (''). NULL signifies that the data value is missing or unknown.

  • If a column is not defined as NOT NULL, it can contain NULL values.

The Problem with Comparison

Because NULL means 'unknown,' standard comparison operators (=, >) do not work with it. If you ask if NULL = NULL, the answer is unknown, so the result is neither true nor false.

Special Operators for NULL

We must use the special operators IS NULL or IS NOT NULL in the WHERE clause.

Example 1: Finding Missing Data

Find all customers who have not provided an email address.

sql SELECT CustomerName FROM Customers WHERE Email IS NULL;

Example 2: Finding Completed Data

Find all products that have a non-null description.

sql SELECT Name FROM Products WHERE Description IS NOT NULL;

Impact on Calculations

If you perform an arithmetic operation involving a NULL value, the result of the entire operation is typically NULL. This is why handling NULL carefully is crucial when calculating totals or averages.