Why MySQL’s != on Nullable Indexed Columns Returns Unexpected Results

Experiment Setup

We create a MySQL table user with columns id int (non‑unique index) and name varchar(20). The index is defined on id, which is allowed to contain NULL values.

create table user (
  id int,
  name varchar(20),
  index(id)
) engine=innodb;

Insert three rows with explicit id values:

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

First Query Without NULL

Query select * from user where id!=1; returns the two rows with id 2 and 3. The EXPLAIN output shows type=ALL (full table scan) because a negative comparison cannot use the index.

explain select * from user where id!=1;

Insert a NULL Row and Query Again

We insert a row that only provides name, leaving id as NULL: insert into user(name) values('wangwu'); Running the same query select * from user where id!=1; now returns only two rows (ids 2 and 3); the row with NULL is omitted. This occurs because the predicate id!=1 evaluates to UNKNOWN for NULL values, and MySQL excludes them from the result set.

select * from user where id!=1;

Correcting the Result with OR

To include the NULL row, the condition must be extended:

select * from user where id!=1 or id is null;

Impact of OR on Index Usage

Adding an OR on the same indexed column forces MySQL to perform a full table scan, as shown by EXPLAIN (type=ALL, rows=4). The scan defeats the benefit of the index.

explain select * from user where id!=1 or id is null;

Union as an Alternative

Separating the two predicates into independent queries and combining them with UNION preserves index usage for each part:

select * from user where id!=1
union
select * from user where id is null;

Both sub‑queries use the index ( type=ref, rows≈1), and the final result matches the expected three rows.

Key Takeaways

Negative comparisons ( != or <>) on indexed columns trigger full table scans.

If the column permits NULL, a != predicate does not return the NULL rows, which can lead to unexpected result sets.

Appending OR column IS NULL fixes the logical result but may cause another full scan.

Using UNION of two simple predicates keeps index usage while delivering the correct rows.

Defining a default (non‑NULL) value for the column avoids the “NULL pitfall” altogether.

The EXPLAIN statement is essential for diagnosing index usage.