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.

Hayley

MyISAM has no trouble storing null. The semantics of NULLs themselves should be independent of engine. – Skinned

Answer 1

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

Jud

It is Unknown - Not false. SQL uses three valued logic. – Embrangle

NULL = NULL is not really FALSE - it's NULL again. But it's not TRUE either, so IF(NULL = NULL) won't execute. – Ruffina

see also @obe answer: SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; -> 1, 1, 0 – Hostess

Not only is null not equal to anything, it's not not-equal to anything, either. In other words, select * from foo where bar <> "abc" will not return rows where bar is null. That threw me for a loop today. The docs call <> the "not equal to" operator, but really it's the "is equal to something other than" operator. – Belcher

Answer 2

SELECT pid FROM planets WHERE userid IS NULL

Austerlitz

Answer 3

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

Importunity

Answer 4

There's also a <=> operator:

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 1.

SELECT 42 <=> 42 yields 1 as well.

See here: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to

Germiston

Answer 5

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 =, <, or <>.

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

Homoiousian

Answer 6

SELECT pid FROM planets WHERE userid is null;

Lemieux

Answer 7

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.

Firenze

Answer 8

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] = ''

Slacken

If that worked for you, the default for your varchar(1) is '' rather than null, therefore unrelated to this question. – Eluviation

Answer 9

Replace the code with this one it will work

SELECT pid FROM planets WHERE userid IS NULL

Muster

Please don't duplicate existing answers, unless you want to share new insights – Osculum