MySQL selecting rows where a column is null

Question

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

User · Answer

There s also a  lt   gt  operator   SELECT pid FROM planets WHERE userid  lt   gt  NULL   Would work  The nice thing is that  lt   gt  can also be used with non-NULL values   SELECT NULL  lt   gt  NULL yields 1   SELECT 42  lt   gt  42 yields 1 as well   See here  https   dev mysql com doc refman 5 7 en comparison-operators html operator equal-to

User · Answer

SELECT pid FROM planets WHERE userid is null

User · Answer

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   Emphasis mine   To search for column values that are NULL  you cannot use an expr   NULL test  The following statement returns no rows  because expr   NULL is never true for any expression  Solution 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 NULL  operator IS NOT NULL tests whether a value is not NULL  MySQL comparison operators

User · Answer

Info from http   w3schools com sql sql null values asp      1  NULL values represent missing unknown data       2  By default  a table column can hold NULL values       3  NULL values are treated differently from other values      4  It is not possible to compare NULL and 0  they are not equivalent       5  It is not possible to test for NULL values with comparison   operators  such as     lt   or  lt         6  We will have to use the IS NULL and IS NOT NULL operators instead   So in case of your problem   SELECT pid FROM planets WHERE userid IS NULL

User · Answer

I had the same issue when converting databases from Access to MySQL  using vb net to communicate with the database    I needed to assess if a field  field type varchar 1   was null   This statement worked for my scenario   SELECT   FROM  table name  WHERE  field name

User · Answer

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

User · Answer

Had the same issue where query   SELECT   FROM  column  WHERE  column  IS NULL     returned no values  Seems to be an issue with MyISAM and the same query on the data in InnoDB returned expected results   Went with   SELECT   FROM  column  WHERE  column            Returned all expected results

User · Answer

SELECT pid FROM planets WHERE userid IS NULL

[mysql] MySQL: selecting rows where a column is null

Examples related to mysql

Examples related to null