如何在T-SQL中获取WHERE IN()语句中的缺失值？

这个场景我熟，你原来用IN的写法只会返回原表中存在的记录，自然会漏掉不存在的E。要实现你要的效果，核心思路是把你要查询的客户端列表先构造成一个独立的数据集，再用左连接（LEFT JOIN）关联原表，这样所有要查的客户端都会被保留，不存在的对应IDs就会是NULL。

基础实现：返回每个客户端对应的IDs（含NULL） #

先写通用的写法，生成包含所有目标客户端的临时表，再左连原表：

SELECT t2.Clients, t1.IDs
FROM (
    -- 这里列出你要查询的所有客户端，按你想要的顺序排列
    SELECT 'A' AS Clients UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'E' UNION ALL
    SELECT 'C'
) t2
LEFT JOIN table1 t1 ON t2.Clients = t1.Clients
-- 保持输出顺序和你输入的一致，不同数据库排序写法略有不同
ORDER BY 
    CASE t2.Clients
        WHEN 'A' THEN 1
        WHEN 'B' THEN 2
        WHEN 'E' THEN 3
        WHEN 'C' THEN 4
    END;

执行这个语句会得到：

Clients | IDs
--------|-----
A       | 1
B       | 2
E       | NULL
C       | 3

进阶：合并成逗号分隔的字符串（如你期望的1,2,NULL,3） #

如果你需要把结果直接合并成逗号分隔的格式，不同数据库的字符串聚合函数不一样，给你几个常用的例子：

MySQL 版本

用GROUP_CONCAT聚合，同时用IFNULL把NULL转成字符串'NULL'：

SELECT GROUP_CONCAT(IFNULL(t1.IDs, 'NULL') ORDER BY FIELD(t2.Clients, 'A','B','E','C')) AS result
FROM (
    SELECT 'A' AS Clients UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'E' UNION ALL
    SELECT 'C'
) t2
LEFT JOIN table1 t1 ON t2.Clients = t1.Clients;

PostgreSQL 版本

用STRING_AGG，配合COALESCE处理NULL：

SELECT STRING_AGG(COALESCE(t1.IDs::TEXT, 'NULL'), ',' ORDER BY 
    CASE t2.Clients
        WHEN 'A' THEN 1
        WHEN 'B' THEN 2
        WHEN 'E' THEN 3
        WHEN 'C' THEN 4
    END
) AS result
FROM (
    -- 用数组快速生成客户端列表
    SELECT unnest(ARRAY['A','B','E','C']) AS Clients
) t2
LEFT JOIN table1 t1 ON t2.Clients = t1.Clients;

SQL Server 版本

用STRING_AGG和ISNULL，配合STRING_SPLIT生成客户端列表：

SELECT STRING_AGG(ISNULL(CAST(t1.IDs AS VARCHAR(10)), 'NULL'), ',') WITHIN GROUP (ORDER BY 
    CASE t2.Clients
        WHEN 'A' THEN 1
        WHEN 'B' THEN 2
        WHEN 'E' THEN 3
        WHEN 'C' THEN 4
    END
) AS result
FROM (
    -- 拆分字符串生成客户端列表
    SELECT value AS Clients FROM STRING_SPLIT('A,B,E,C', ',')
) t2
LEFT JOIN table1 t1 ON t2.Clients = t1.Clients;

关键要点总结 #

1. 不要用IN过滤原表，而是把查询目标做成基准数据集，这样才能保留所有要查的项；
2. LEFT JOIN会保留基准数据集中的所有记录，原表中不存在的项对应字段自动为NULL；
3. 一定要注意排序，如果不指定顺序，数据库可能会乱序返回，不符合你的预期。

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