MySQL Null Values

What Is a NULL Value?

A field with a NULL value is a field that has been left empty during record creation. NULL is not the same as zero, and it is not the same as a string containing spaces. A field with a NULL value has no value at all — it simply means 'unknown' or 'not applicable'. For example, an optional 'MiddleName' column might be NULL for a customer who has no middle name, whereas a 'PhoneNumber' column might be NULL because that data was never collected.

Why You Cannot Use = or != With NULL

It is not possible to test for NULL values with comparison operators such as =, <, or !=. Because NULL represents an unknown quantity, comparing anything to it also produces an unknown result rather than TRUE or FALSE. This means a query like WHERE column_name = NULL will never match any rows, even rows that are actually NULL, and it will not raise an error either — it will silently return zero rows, which is a common source of confusion for beginners.

The IS NULL and IS NOT NULL Operators

MySQL provides two dedicated operators for working with NULL values: IS NULL, which returns TRUE when a column's value is NULL, and IS NOT NULL, which returns TRUE when a column has any actual value. These operators must be used instead of the standard equality operators whenever you need to filter rows based on missing data.

• IS NULL — selects rows where the column has no value
• IS NOT NULL — selects rows where the column has a value
• NULL is never equal to another NULL when compared with =
• Functions like IFNULL() and COALESCE() can substitute a default value for NULL

Selecting Rows With Missing Data

Suppose a 'Customers' table has an 'Address' column that is sometimes left blank. To find every customer without a recorded address, you would filter on IS NULL rather than trying to match an empty string, since an empty string and NULL are stored differently and mean different things.

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

Selecting Rows That Have a Value

The opposite case is just as common: you may want to report only on customers whose address is known, so that a mailing list does not include incomplete records. IS NOT NULL handles this cleanly.

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

Combining NULL Checks With Other Conditions

IS NULL and IS NOT NULL combine with AND, OR, and other conditions just like any other predicate, which lets you build precise reports, such as finding orders that have shipped but have no recorded carrier reference yet.

SELECT OrderID, ShippedDate, TrackingNumber
FROM Orders
WHERE ShippedDate IS NOT NULL
  AND TrackingNumber IS NULL;