如何在MySQL视图无结果时返回指定值？

当前视图生成的MySQL记录如下：

+---------+-----------------------------+------------+
| user_id | meta_key                    | meta_value |
+---------+-----------------------------+------------+
|       2 | admin-first-timer-training  | NULL       |
|       2 | admin-first-timer-overnight | No         |
|       2 | admin-first-timer-outdoor   | No         |
+---------+-----------------------------+------------+

期望得到的记录：

+---------+-----------------------------+------------+
| user_id | meta_key                    | meta_value |
+---------+-----------------------------+------------+
|       2 | admin-first-timer-training  | Yes        |
|       2 | admin-first-timer-overnight | No         |
|       2 | admin-first-timer-outdoor   | No         |
+---------+-----------------------------+------------+

当前使用的视图创建语句：

create VIEW `wp_record_db` AS 
select distinct `u`.`ID` AS `id`,
    (select distinct (
        CASE WHEN `wp_usermeta`.`meta_value`="No" THEN "No" 
              ELSE "Yes" END) 
    from `wp_usermeta` 
    where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` in ('admin-first-timer-outside','admin-first-timer-outdoors','admin-first-timer-outdoor') 
    limit 1) AS `admin-first-timer-outdoor`,
    (select distinct (
        CASE WHEN `wp_usermeta`.`meta_value`="No" THEN "No" 
             WHEN `wp_usermeta`.`meta_value` IS NULL THEN "YES" 
             ELSE "Yes" END) 
    from `wp_usermeta` where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` = 'admin-first-timer-training' 
    limit 1) AS `admin-first-timer-training`,
    (select distinct (
        CASE WHEN `wp_usermeta`.`meta_value`="No" THEN "No" 
             ELSE "Yes" END) 
    from `wp_usermeta` 
    where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` = 'admin-first-timer-overnight' 
    limit 1) AS `admin-first-timer-overnight` 
from `wp_users` `u`;

尝试在admin-first-timer-training的子查询中用CASE语句判断NULL值，但未得到预期结果。单独执行SELECT IFNULL((select wp_usermeta.meta_valuefromwp_usermetawherewp_usermeta.user_id= 2 andwp_usermeta.meta_key = 'admin-first-timer-training'), "Yes");能得到正确输出，但整合到CASE后总是返回空值。

问题核心在于：当wp_usermeta中不存在该用户的admin-first-timer-training记录时，子查询本身会返回NULL，此时外层的CASE判断无法触发；只有当存在记录但meta_value为NULL时，原CASE逻辑才会生效。以下是两种可行的修复方案：

方案1：用IFNULL包裹子查询并结合CASE判断 #

直接将IFNULL的逻辑整合到子查询中，同时保留对"No"值的判断：

(select CASE WHEN IFNULL(`wp_usermeta`.`meta_value`, "Yes") = "No" THEN "No" ELSE "Yes" END
 from `wp_usermeta` 
 where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` = 'admin-first-timer-training' 
 limit 1) AS `admin-first-timer-training`

这个写法会先把NULL值替换为"Yes"，再判断是否为"No"，无论是否存在对应记录，都能返回预期结果。

方案2：用UNION ALL补充默认值 #

如果子查询找不到对应记录，就通过UNION ALL补充一条"Yes"的结果，再取第一条：

(select CASE 
         WHEN `wp_usermeta`.`meta_value` = "No" THEN "No"
         ELSE "Yes" 
       END
 from `wp_usermeta` 
 where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` = 'admin-first-timer-training' 
 union all select "Yes" limit 1) AS `admin-first-timer-training`

修改后的完整视图语句 #

create VIEW `wp_record_db` AS 
select distinct `u`.`ID` AS `id`,
    (select distinct (
        CASE WHEN `wp_usermeta`.`meta_value`="No" THEN "No" 
              ELSE "Yes" END) 
    from `wp_usermeta` 
    where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` in ('admin-first-timer-outside','admin-first-timer-outdoors','admin-first-timer-outdoor') 
    limit 1) AS `admin-first-timer-outdoor`,
    -- 采用方案1的写法
    (select CASE WHEN IFNULL(`wp_usermeta`.`meta_value`, "Yes") = "No" THEN "No" ELSE "Yes" END
     from `wp_usermeta` 
     where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` = 'admin-first-timer-training' 
     limit 1) AS `admin-first-timer-training`,
    (select distinct (
        CASE WHEN `wp_usermeta`.`meta_value`="No" THEN "No" 
             ELSE "Yes" END) 
    from `wp_usermeta` 
    where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` = 'admin-first-timer-overnight' 
    limit 1) AS `admin-first-timer-overnight` 
from `wp_users` `u`;

内容的提问来源于stack exchange，提问作者Tim Dobson