如何单条SQL查询未在reserves表room_ids中出现的房间记录

你最初写的SQL语句无法得到正确结果。

原写法的核心问题 #

reserves表的room_ids字段存储的是逗号拼接的ID字符串，比如1,2,3是一整个字符串值，不是拆分后的独立ID。你写的子查询select room_ids from reserves返回的是三行字符串：1,2,3、2,3,4、2,4,5，并不是你预期的[1,2,3,4,5]这个ID集合。
当你用整数类型的rooms.id去和这些字符串做NOT IN匹配时，数据库会触发隐式类型转换，最终匹配逻辑完全错乱，根本筛不出正确结果。

修正的单条SQL写法 #

不同数据库拆分逗号字符串的函数有区别，选对应你环境的写法即可：

MySQL 环境（兼容5.7及以上版本） #

用序列表拆分字符串后做反连接：

SELECT r.*
FROM rooms r
LEFT JOIN (
    SELECT DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(res.room_ids, ',', seq.n), ',', -1)) AS room_id
    FROM reserves res
    INNER JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
        -- 这里的数字最大值只要大于room_ids里最多存的ID数量即可
    ) seq ON seq.n <= LENGTH(res.room_ids) - LENGTH(REPLACE(res.room_ids, ',', '')) + 1
) reserved ON r.id = reserved.room_id
WHERE reserved.room_id IS NULL;

如果是MySQL 8.0+，可以用CTE+JSON_TABLE简化写法：

WITH reserved_rooms AS (
    SELECT DISTINCT jt.room_id
    FROM reserves res,
    JSON_TABLE(
        CONCAT('["', REPLACE(res.room_ids, ',', '","'), '"]'),
        '$[*]' COLUMNS (room_id INT PATH '$')
    ) jt
)
SELECT * FROM rooms WHERE id NOT IN (SELECT room_id FROM reserved_rooms);

PostgreSQL 环境 #

直接用内置的字符串拆分函数即可：

SELECT * FROM rooms
WHERE id NOT IN (
    SELECT DISTINCT unnest(string_to_array(room_ids, ','))::INT
    FROM reserves
);

额外建议 #

把多个ID用逗号拼接存在单个字段里是违反数据库设计第一范式的做法，后续做关联查询、条件过滤、加索引都会非常麻烦。更合理的设计是单独建一张预约-房间关联表，每条记录存一个预约ID和对应的单个房间ID，后续所有关联查询都会简单很多，性能也更好。
按照你提供的测试数据，最终正确查询结果是id为6、7的两条房间记录：

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