The best answers to the question “MySQL: selecting rows where a column is null” in the category Dev.
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.
SELECT pid FROM planets WHERE userid IS NULL
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).
Rule 3 https://en.wikipedia.org/wiki/Codd%27s_12_rules
There’s also a
SELECT pid FROM planets WHERE userid <=> NULL
Would work. The nice thing is that
<=> can also be used with non-NULL values:
SELECT NULL <=> NULL yields
SELECT 42 <=> 42 yields
1 as well.
See here: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to
As all are given answers I want to add little more. I had also faced the same issue.
Why did your query fail? You have,
SELECT pid FROM planets WHERE userid = NULL;
This will not give you the expected result, because from mysql doc
In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.
To search for column values that are
NULL, you cannot use an
expr = NULLtest. The following statement returns no rows, because
expr = NULLis never true for any expression
SELECT pid FROM planets WHERE userid IS NULL;
To test for
NULL, use the
IS NULL and
IS NOT NULL operators.
- operator IS NULL tests whether a value is
- operator IS NOT NULL tests whether a value is not
- MySQL comparison operators