Problems Caused by NULL Values in MySQL Columns and Their Solutions

The author (Wang Lei) from the Java Chinese community demonstrates how NULL values in MySQL columns can lead to unexpected problems and shows how to avoid them with proper SQL techniques.

1. COUNT Data Loss

When a column contains NULL, using COUNT(column) skips the NULL rows, causing the count to be smaller than the total number of rows. The correct approach is to use COUNT(*) , which counts all rows regardless of NULL values.

select count(*), count(name) from person;

Solution

Use COUNT(*) for row statistics instead of COUNT(column) or COUNT(constant) .

2. DISTINCT Data Loss

When applying COUNT(DISTINCT col1, col2) , if any column in the distinct set is NULL, the entire row is omitted from the distinct count, even if the other column has a unique value.

select count(distinct name, mobile) from person;

The result shows fewer distinct rows than expected because rows with NULL in name are ignored.

3. SELECT Data Loss with Inequality

Using WHERE name <> 'Java' or WHERE name != 'Java' excludes rows where name is NULL, because any comparison with NULL yields UNKNOWN. To include those rows, add an explicit NULL check.

select * from person where name <> 'Java' or isnull(name) order by id;

4. NULL Leading to NULL in SUM and Potential NPE

If a column used in SUM() contains only NULL values for the filtered rows, the result of SUM() is NULL, not 0. This can cause a NullPointerException in application code.

select sum(num) from goods where id > 4;

To avoid the NPE, wrap the aggregation with IFNULL() (or COALESCE() ) so that a zero is returned instead of NULL.

select ifnull(sum(num), 0) from goods where id > 4;

5. Increased Query Difficulty

When a column may contain NULL, ordinary comparison operators (=, !=, <>) cannot be used reliably. Queries must use IS NULL , IS NOT NULL , or functions like ISNULL(column) , which adds complexity for beginners.

-- Wrong
select * from person where name <> null;
select * from person where name != null;

-- Correct
select * from person where name is not null;
select * from person where !isnull(name);

Recommended Practice

Alibaba's Java Development Manual recommends using ISNULL(column) to test for NULL because it is concise and performs slightly better.

NULL Does Not Affect Index Usage

Even if a indexed column contains NULL values, MySQL can still use the index for queries. An EXPLAIN on a query filtering by the indexed column shows that the index is chosen regardless of NULL presence.

Summary

The article outlines five pitfalls caused by NULL values in MySQL columns—loss of query results, NULL‑induced NULL aggregates leading to NPE, increased query difficulty, and confirms that NULL does not hinder index usage. It advises defining NOT NULL constraints where possible, or using default placeholders (empty string or 0) to avoid these issues.