tags:

views:

120

answers:

1

is its somehow possible to do a select for empty strings and NULL values in Mysql without using or?

this:

   select * from table where col IN (null, "");

doesnt work, it ignores the null (or possibly matches it with the string 'null'

thanks, P.V. Goddijn

+5  A: 
SELECT  *
FROM    mytable
WHERE   COALESCE(col, '') = ''

Note, however, than OR query will be much more efficient if the column is indexed:

SELECT  *
FROM    mytable
WHERE   col = '' OR col IS NULL

This will use ref_or_null access path on the index.

If you need to select from a list of values along with NULLs, just put all not-null values into the list and add a single OR IS NULL condition:

SELECT  *
FROM    mytable
WHERE   col IN ('val1', 'val2', 'val3') OR col IS NULL

This will use an index on col as well.

Quassnoi
i left it out from consciseness, but in practice my query would be:select * from table where col IN ("", NULL, "a", "b", "c", "few more options");would this still be correctly using an index?( col in (list) or IS NULL )
pvgoddijn
`@pvgoddjin`: yes, it will.
Quassnoi