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.

A nullable int column can be either an int or NULL - it can't be an empty string. — Sam Dufel, Feb 05 '13 at 17:24
is content_id a int column or a varchar column? if it's an int column there's no need to check if content_id='' so I think I'll delete my answer :) — fthiella, Feb 05 '13 at 18:17

score 9 · Accepted Answer · answered Feb 05 '13 at 17:21

9

You can use IFNULL function in MySQL.

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

answered Feb 05 '13 at 17:21

Pablo Santa Cruz

score 4 · Answer 2 · edited Dec 12 '22 at 03:40

4

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:

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

edited Dec 12 '22 at 03:40

Marnix.hoh

answered Feb 05 '13 at 18:14

fthiella

score 2 · Answer 3 · answered Feb 05 '13 at 17:23

2

You can try COALESCE:

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

answered Feb 05 '13 at 17:23

bonCodigo

Coalesce is mobile to all RDBMS platforms. Check here for a comparison of [SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions](http://www.w3schools.com/sql/sql_isnull.asp) – bonCodigo Feb 05 '13 at 17:32

3 Answers3