如何让SELECT查询IN子句中无匹配值时返回对应提示信息？

没问题！要实现这个需求，核心思路是先构造一个包含所有你要查询的用户名的临时数据集，再和原表做左连接——这样就能把不存在的用户也保留在结果里，最后用条件判断输出对应的提示。下面分几种常见的SQL环境给你具体写法：

MySQL/MariaDB 写法 #

用UNION ALL生成包含目标用户名的临时表，再左连接原表：

SELECT 
    t.UserName,
    CASE 
        WHEN p.Location IS NOT NULL THEN p.Location
        ELSE CONCAT(t.UserName, ' is not in People_DB')
    END AS Location_Info
FROM (
    SELECT 'Nick' AS UserName
    UNION ALL SELECT 'Jack'
    UNION ALL SELECT 'Robert'
) t
LEFT JOIN People_DB p ON t.UserName = p.UserName;

SQL Server 写法 #

用VALUES子句更简洁地生成临时数据集：

SELECT 
    t.UserName,
    CASE 
        WHEN p.Location IS NOT NULL THEN p.Location
        ELSE CONCAT(t.UserName, ' is not in People_DB')
    END AS Location_Info
FROM (
    VALUES ('Nick'), ('Jack'), ('Robert')
) t(UserName)
LEFT JOIN People_DB p ON t.UserName = p.UserName;

PostgreSQL 写法 #

同样可以用VALUES子句，注意标识符的大小写（如果你的表名是小写的话）：

SELECT 
    t.username,
    CASE 
        WHEN p.location IS NOT NULL THEN p.location
        ELSE concat(t.username, ' is not in People_DB')
    END AS location_info
FROM (
    VALUES ('Nick'), ('Jack'), ('Robert')
) AS t(username)
LEFT JOIN people_db p ON t.username = p.username;

效果说明 #

执行以上任意一种查询后，结果集会包含三条记录：

• Nick 和 Jack 会显示他们对应的Location值
• Robert 会显示Robert is not in People_DB的提示文本

如果需要把提示信息单独放在一列（比如新增Status列），也可以调整CASE语句的逻辑，比如：

-- 以MySQL为例，新增状态列
SELECT 
    t.UserName,
    p.Location,
    CASE 
        WHEN p.Location IS NOT NULL THEN 'Exists'
        ELSE CONCAT(t.UserName, ' is not in People_DB')
    END AS Status
FROM (
    SELECT 'Nick' AS UserName
    UNION ALL SELECT 'Jack'
    UNION ALL SELECT 'Robert'
) t
LEFT JOIN People_DB p ON t.UserName = p.UserName;

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