如何实现指定request_id不存在时返回null值？

现有示例表acts的结构和数据如下：

原查询语句：

select request_id, id
from acts
where id in (234,531,876)

无法得到包含876对应null值的结果，需要输出如下目标结果：

要求对in列表中不存在于acts表request_id中的值，返回null作为对应的id字段值，该如何实现？

核心思路是先构造包含所有目标request_id的临时数据集，再与原表做左连接，这样就能保留所有目标值，不存在的记录自然会返回null。

方法1：构造临时行集合（通用多数数据库） #

直接用VALUES子句生成包含所有目标request_id的临时表，再和acts表左关联：

SELECT t.target_request_id AS request_id, a.id
FROM (
    VALUES (234), (531), (876)
) AS t(target_request_id)
LEFT JOIN acts a ON a.request_id = t.target_request_id;

适配不同数据库的细节 #

• MySQL 8.0及以上支持上述写法；如果是MySQL低版本，可改用UNION ALL构造临时集：

  SELECT t.target_request_id AS request_id, a.id
  FROM (
      SELECT 234 AS target_request_id
      UNION ALL SELECT 531
      UNION ALL SELECT 876
  ) AS t
  LEFT JOIN acts a ON a.request_id = t.target_request_id;
  

• SQL Server同样支持VALUES写法，或者用UNION ALL实现。

方法2：数组转行（以PostgreSQL为例） #

PostgreSQL可以用unnest函数直接将数组转为行数据集，再做左连接：

SELECT t.target_request_id AS request_id, a.id
FROM unnest(ARRAY[234,531,876]) AS t(target_request_id)
LEFT JOIN acts a ON a.request_id = t.target_request_id;

关键说明 #

原查询的问题在于WHERE条件是过滤原表的现有行，只会返回原表中存在的记录，无法生成原表没有的行。而左连接会完整保留左表（临时目标集）的所有行，右表（acts）中匹配不到的字段就会自动填充null，完美契合需求。

内容的提问来源于stack exchange，提问作者Альберт Александров