How to select data from MySQL table where column is NULL

Question

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

SELECT * FROM pubco WHERE exchange IS NULL; 

Answer 1

You may have interpreted '' as NULL , which is not the same, but try this

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

but if you still don't get the value, maybe there are spaces on it, so you should TRIM it,

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

Answer 2

One important note: NULL and '' (i.e. an empty row) are not the same thing, most likely your column contains empty rows, so you need to add another condition:

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

Answer 3

Are you sure they are zero?

Perhaps they have a string value of "NULL", this is not the same as what it returns if you do:

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

Answer 4

Instead of using this query

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

try it

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

This will improve performance. .