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!