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 to write this condition.

I have an int () column, which can be either: NULL, 0 or EMPTY.

Answer 1

You can use the IFNULL function in MySQL.

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

Answer 2

I think the shorter way is:

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

content_id IN ('0', '') can take these values:

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

If it is Null, with COALESCE I return 1, which is equivalent to True.

Answer 3

You can try COALESCE :

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