mysql if not null, 0 or ""

Question

SELECT * 
  FROM table WHERE id IN ('21') 
   AND (content_id IS NULL OR content_id = 0 OR content_id = '')

Is there a shorter way of writing this condition.

I have a int() column that could be either: NULL, 0 or EMPTY.

Answer 1

Could this work for you?

SELECT *
FROM table
WHERE id IN ('21')
AND content_id IN (NULL, 0, '');

Answer 2

I think the shorter way is this:

SELECT * 
FROM table
WHERE id IN ('21')
      AND COALESCE(content_id IN ('0', ''), 1)

content_id IN ('0', '') may assume these values:

• True if content_id is either '0' or ''
• Null if content_id IS Null
• False otherwise.

If it's Null, COALESCE will return 1 here, which is equivalent to True.

Answer 3

You can try COALESCE:

SELECT * FROM table WHERE id IN ('21') 
AND COALESCE(content_id,0) =0;

Answer 4

You can use IFNULL function in MySQL.

select ____
  from tbl
 where IFNULL(content_id, 0) = 0