Why Does MySQL's `!=` Operator Exclude Rows with NULL Values?

MySQL: Understanding NULL Value Comparisons

Overview:
Working effectively with MySQL necessitates a clear understanding of how NULL values behave within comparison operations. This article focuses on the behavior of NULL values when using the != (not equal) operator.

The Problem: Suppose a table includes a CODE column that allows NULL values. Why does the query below omit rows where CODE is NULL, even though != is used?

SELECT * FROM TABLE WHERE CODE!='C'

The Solution: MySQL's != operator doesn't produce a true/false result when comparing a non-NULL value to NULL. The comparison is considered indeterminate.

To correctly handle NULLs, use IS NULL or IS NOT NULL. The following query accurately excludes rows with CODE equal to 'C' or NULL:

SELECT * FROM TABLE WHERE CODE IS NULL OR CODE!='C'

This approach ensures both conditions are evaluated independently, excluding only rows satisfying either condition.

Further Clarification: While sometimes seen in MySQL documentation or forums, CODE != '' is not a substitute for CODE IS NOT NULL. The != operator compares values; IS NULL checks for the absence of a value.

Therefore, these queries are not equivalent:

SELECT * FROM TABLE WHERE CODE != ''
SELECT * FROM TABLE WHERE CODE IS NOT NULL

Using IS NULL and IS NOT NULL ensures proper handling and testing of NULL values in MySQL queries.

The above is the detailed content of Why Does MySQL's `!=` Operator Exclude Rows with NULL Values?. For more information, please follow other related articles on the PHP Chinese website!