Handling empty set in MySQL CASE statement

case, mysql, null

Solution

If it's a scalar subquery, (i. e. you use in a `SELECT` or `WHERE` clause, not in FROM clause), then use this:

IF(
EXISTS
(
SELECT  NULL
FROM    sample_table t
WHERE   t.user_id = 2
        AND t.group_id = 12
), 'yes', 'no'
)

or even this:

COALESCE(
(
SELECT  'yes'
FROM    sample_table t
WHERE   t.user_id = 2
        AND t.group_id = 12
), 'no')

Problem

MySQL server version 5.0.45. Consider the following: ``` ( SELECT CASE WHEN t.group_id = 12 THEN 'yes' ELSE 'no' END FROM sample_table t WHERE t.user_id = 2 AND t.group_id = 12 ) as foo ``` This subquery of a larger statement works as I'd expect, yielding a 'yes' or 'no' string value most of the time. It's not ideal, but that's what you get for working on someone else's code! However, sometimes the select can legitimately return an empty set, in which case foo is set to NULL. This does not actually break the application, but it's irritating. Is there a way I can guarantee that foo will always be either 'yes' or 'no', even if there are no matching rows in the table?

Original source