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

Question

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;

Either the table is empty or the there isn't any null value in the exchange column — Sashi Kant
– Sashi Kant, Commented Jan 11, 2013 at 14:15
there's no row with exchange NULL ! (maybe a DEFAULT value in the exchange column ?) — Raphaël Althaus
– Raphaël Althaus, Commented Jan 11, 2013 at 14:15
Perhaps what you think is NULL is actually the empty string ''. — Michael Berkowski
– Michael Berkowski, Commented Jan 11, 2013 at 14:16

John Woo · Accepted Answer · 2013-01-11 14:16:14Z

6

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

answered Jan 11, 2013 at 14:16

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Over a year ago

you have nailed it. The problem was my interpretation of NULL vs. '' thanks for your help. Will accept answer when I can.

Bud Damyanov · Accepted Answer · 2013-01-11 14:17:28Z

0

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 = '');

answered Jan 11, 2013 at 14:17

Bud Damyanov

32.1k7 gold badges47 silver badges53 bronze badges

Comments

Santiago Hernandez · Accepted Answer · 2013-01-11 14:17:36Z

0

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";

answered Jan 11, 2013 at 14:17

Santiago Hernandez

3062 silver badges8 bronze badges

Comments

Prahalad Gaggar · Accepted Answer · 2013-01-11 14:24:14Z

0

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.

answered Jan 11, 2013 at 14:24

Prahalad Gaggar

11.6k17 gold badges57 silver badges73 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related