Home > Database > Mysql Tutorial > Why Does MySQL's `!=` Operator Exclude Rows with NULL Values?

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

Susan Sarandon
Release: 2025-01-09 19:31:42
Original
1059 people have browsed it

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'
Copy after login

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'
Copy after login

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
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template