MySQL: select * from table where col IN (null, "") possible without OR

Question

Is it 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, ""); 

doesn't work, it ignores the null (or possibly matches it with the string 'null').

Answer 1

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.