How to select data from MySQL table where a column is NULL

4
votes

I expect the following code to show me all the records in the table where the exchange column is null but the result set show 0 rows. Any idea why?

SELECT * FROM pubco WHERE exchange IS NULL;
4
mysqlsqlselect
Either the table is empty or the there isn't any null value in the exchange column - Sashi Kant
there's no row with exchange NULL ! (maybe a DEFAULT value in the exchange column ?) - Raphaël Althaus
Perhaps what you think is NULL is actually the empty string ''. - Michael Berkowski

4 Answers

5
votes

maybe you have interpreted '' as NULL which is not the same, but try this

SELECT * 
FROM pubco 
WHERE exchange IS NULL OR
      exchange = ''

but if still not getting the value, maybe it has spaces on it, so you should TRIM it,

SELECT * 
FROM pubco 
WHERE exchange IS NULL OR
      TRIM(exchange) = ''
0
votes

One important remark: NULL and '' (i.e. empty string) are not the same thing, most probably your column contains empty strings, so you need to put another condition:

SELECT * FROM pubco WHERE (exchange IS NULL OR exchange = '');
0
votes

Are you sure they are null?

Maybe they have a string value "NULL" that is not the same, what does it returns if you do:

SELECT * FROM pubco WHERE exchange == "NULL";
0
votes

Rather than using the this query

SELECT * 
FROM pubco 
WHERE exchange IS NULL OR
      exchange = ''

Try this

SELECT * FROM pubco 
WHERE isnull(exchange,'')='';

It will improve your performance.