Handling empty set in MySQL CASE statement

Question

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?

Answer 1

If you want the empty set to represent a "no" case, you could probably do this instead:

select ... where ... (foo = 'no' or foo is null)

This would handle both casees without having to drastically change your subquery.

Answer 2

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