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

pvgoddijn_15355 0
Viewed 30892

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

null mysql
3 Answers
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.

user_551590

In case anyone else is looking for this solution, as I was; if you're searching by more than one column, you need to group the where/or statement in parenthesis.

This will not return the results you expect:

SELECT * 
FROM table 
WHERE col1 IN ('val1', 'val2') OR col1 IS NULL AND col2 IN ('val3', 'val4')

The "OR col1 IS NULL" will completely override your "AND col2 IN" statement.

Putting all of the conditions for col1 inside parenthesis will work:

SELECT * 
FROM table 
WHERE (col1 IN ('val1', 'val2') OR col1 IS NULL) AND col2 IN ('val3', 'val4')

It took me a while to consider trying this, because (at least in my experience) you don't typically put WHERE statements in parenthesis.

user_101663340

That's work great for me

SELECT *
FROM table
WHERE IFNULL(col1, '-') IN('val1', 'val2', '-') AND col2 IN ('val3', 'val4')
user_187337120
Related