Why Does MySQL Exclude NULL Values When Using the != Operator in a WHERE Clause?

MySQL comparison with NULL values

Understanding how MySQL handles these comparisons is critical when dealing with columns that allow NULL values, such as the CODE column in the example. Why does the following query exclude rows where CODE is NULL?

<code class="language-sql">SELECT * from TABLE where CODE!='C'</code>

This is because the != (not equal to) operator does not explicitly test for NULL values. It only checks if the value is not equal to 'C'. Since NULL is a special value that indicates a missing value, it is neither equal nor unequal to any specific value.

To include NULL values, you must use the IS NULL or IS NOT NULL operator. These operators explicitly check for the presence of NULL values. The correct query should be:

<code class="language-sql">SELECT * from TABLE where CODE IS NULL OR CODE!='C'</code>

By using IS NULL or IS NOT NULL, the query ensures that it contains rows where CODE is NULL, as well as rows where CODE is not equal to 'C'. This is because the OR operator treats both conditions as valid criteria for matching rows.

In summary, when comparing columns that may contain NULL values, always use the IS NULL or IS NOT NULL operator to explicitly test for the presence of NULL values. Otherwise, the comparison results may not be as expected.

The above is the detailed content of Why Does MySQL Exclude NULL Values When Using the != Operator in a WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!