Why Does 'CODE!='C'' Exclude NULL Values in MySQL Queries?

Comparison of NULL values ​​in MySQL

In MySQL, comparing columns containing NULL values ​​can present challenges because the behavior is different from standard equality comparisons. This article explores why a query like "SELECT * from TABLE where CODE!='C'" excludes rows with CODE=NULL, even if 'C' is not NULL.

NULL comparison behavior

When comparing a column to a non-NULL value (such as "CODE!='C'"), the query returns rows where CODE matches 'C' and excludes all other values, including NULL. This behavior results from SQL treating NULL as a unique value that does not participate in standard comparisons.

To include NULL values ​​in a comparison, they must be checked explicitly using the IS NULL or IS NOT NULL operator. For example, the following query will return all rows where CODE is NULL or not equal to 'C':

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

Explanation of CODE!='C' behavior

The reason "CODE!='C'" does not return rows with CODE=NULL is that the query will evaluate to false for any comparison involving NULL. Even if 'C' is not NULL, comparing it to a NULL value will always result in false.

Alternative comparison operators

MySQL provides an alternative comparison operator "<=" (less than or equal to) that can be used to compare values ​​that contain NULL. The evaluation of "x <= y" returns true if x is less than, equal to, or NULL, false otherwise.

You can return all rows where CODE is NULL or not equal to 'C' by using "CODE <= 'C' OR CODE IS NULL" instead of "CODE!='C'".

The above is the detailed content of Why Does 'CODE!='C'' Exclude NULL Values in MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!