Handling an empty set in a 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 the larger operator works as I expected, getting the string value "yes" or "no" most of the time. This is not ideal, but what you get for working on another user's code!

However, sometimes a selection may legitimately return an empty set, in which case foo is NULL. This does not actually violate the application, but it is annoying. Is there a way to guarantee that foo will always be yes or no, even if there are no matching rows in the table?

Answer 1

(, SELECT WHERE, FROM), :

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

:

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

Answer 2

, "", :

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

.