MySQL: selecting rows where a column is null

mysql null
The.Anti.9 picture The.Anti.9 · Aug 21, 2010 · Viewed 404.3k times · Source

I'm having a problem where when I try to select the rows that have a NULL for a certain column, it returns an empty set. However, when I look at the table in phpMyAdmin, it says null for most of the rows.

My query looks something like this:

SELECT pid FROM planets WHERE userid = NULL

Empty set every time.

A lot of places said to make sure it's not stored as "NULL" or "null" instead of an actual value, and one said to try looking for just a space (userid = ' ') but none of these have worked. There was a suggestion to not use MyISAM and use innoDB because MyISAM has trouble storing null. I switched the table to innoDB but now I feel like the problem may be that it still isn't actually null because of the way it might convert it. I'd like to do this without having to recreate the table as innoDB or anything else, but if I have to, I can certainly try that.

Answer

Marc B picture Marc B · Aug 21, 2010

SQL NULL's special, and you have to do WHERE field IS NULL, as NULL cannot be equal to anything,

including itself (ie: NULL = NULL is always false).

See Rule 3 https://en.wikipedia.org/wiki/Codd%27s_12_rules

Related questions

MySQL and PHP - insert NULL rather than empty string

I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng). Right now, if these values are not entered I pass along an empty string as a value. How do …

Read More
Checking for an empty field with MySQL

I've wrote a query to check for users with certain criteria, one being they have an email address. Our site will allow a user to have or not have an email address. $aUsers=$this->readToArray(' SELECT `userID` FROM `…

Read More
MySql Query Replace NULL with Empty String in Select

How do you replace a NULL value in the select with an empty string? It doesnt look very professional to output "NULL" values. This is very unusual and based on my syntax I would expect it to work. Hoping for …

Read More