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