How to Correctly Select Rows with NULL Values in MySQL?

MySQL: Selecting Rows with NULL Values

While attempting to retrieve rows where a particular column contains a NULL value, the user encounters an empty result set. This discrepancy arises despite the table displaying NULL values when viewed in phpMyAdmin.

The query in question resembles the following:

SELECT pid FROM planets WHERE userid = NULL

After exploring various suggestions, including checking for "NULL" or "null" strings and searching for empty spaces, the user remains unable to retrieve the desired rows. Suspecting that the MyISAM storage engine may be a contributing factor, the user converts the table to InnoDB. However, the issue persists.

Resolving the Issue

The solution lies in understanding the special nature of SQL NULL values. Unlike traditional values, NULLs cannot be equated using the equal sign (=). To correctly retrieve rows with NULL values, the following syntax must be used:

WHERE field IS NULL

This distinction stems from the fact that NULL represents an unknown or undefined value, making it impossible to determine whether it is truly equal to anything, including itself. This principle is enshrined in Codd's Rule 3.

By adjusting the query to:

SELECT pid FROM planets WHERE userid IS NULL

the user should now be able to successfully retrieve the rows with NULL values.

The above is the detailed content of How to Correctly Select Rows with NULL Values in MySQL?. For more information, please follow other related articles on the PHP Chinese website!