子查询返回多行报错：能否使用AND NOT IN？附相关SQL语句

首先直接给结论：是的，你可以用NOT IN替代原来的NOT LIKE来解决这个子查询返回多行的报错，不过得先搞清楚为什么原来的语句会出问题——你用了NOT LIKE搭配一个返回多行结果的子查询，但LIKE/NOT LIKE是用来做单值匹配的，当子查询返回超过一行时，数据库不知道该拿哪一行和外层的r.rom_ID做比较，自然就抛出「Subquery returns more than 1 row」的错误了。

用NOT IN修改后的可用语句 #

把原来的NOT LIKE换成NOT IN，就能正确处理子查询返回的多行结果，修改后的SQL如下：

SELECT r.rom_ID 
FROM rom r 
WHERE r.rom_ID NOT IN (
    SELECT r.rom_ID 
    FROM rom r, booking b 
    WHERE b.rom_ID = r.rom_ID 
      AND ( '2018-05-08' BETWEEN b.ankomstdato AND b.utsjekkdato 
         OR '2018-05-12' BETWEEN b.ankomstdato AND b.utsjekkdato 
         OR ('2018-05-08' <= b.ankomstdato AND '2018-05-12' >= b.utsjekkdato ) )
) 
AND r.romtype_ID = 2 
LIMIT 1;

不过这里要提一个坑：如果子查询返回的结果里包含NULL，NOT IN会导致整个查询返回空结果——因为NULL的比较逻辑是「未知」，数据库会直接排除所有行。如果你的rom_ID不可能为NULL，那这个写法没问题；如果有可能出现NULL，建议用下面的NOT EXISTS方案。

更推荐的健壮方案：用NOT EXISTS #

NOT EXISTS在处理这类存在性检查时，通常比NOT IN更高效，而且完全不会受NULL值的影响，写法也更简洁：

SELECT r.rom_ID 
FROM rom r 
WHERE r.romtype_ID = 2
  AND NOT EXISTS (
      SELECT 1 
      FROM booking b 
      WHERE b.rom_ID = r.rom_ID 
        AND ( '2018-05-08' BETWEEN b.ankomstdato AND b.utsjekkdato 
           OR '2018-05-12' BETWEEN b.ankomstdato AND b.utsjekkdato 
           OR ('2018-05-08' <= b.ankomstdato AND '2018-05-12' >= b.utsjekkdato ) )
) 
LIMIT 1;

这里子查询里不需要再关联rom表，直接用booking和外层的rom关联就行，逻辑更清晰，数据库也更容易做优化。

补充：用LEFT JOIN + IS NULL的写法 #

除了上面两种，你也可以用左连接的思路实现相同逻辑，同样能避免NULL的问题：

SELECT r.rom_ID 
FROM rom r
LEFT JOIN booking b 
  ON b.rom_ID = r.rom_ID 
  AND ( '2018-05-08' BETWEEN b.ankomstdato AND b.utsjekkdato 
     OR '2018-05-12' BETWEEN b.ankomstdato AND b.utsjekkdato 
     OR ('2018-05-08' <= b.ankomstdato AND '2018-05-12' >= b.utsjekkdato ) )
WHERE r.romtype_ID = 2
  AND b.rom_ID IS NULL
LIMIT 1;

这个写法的思路是把所有房间和冲突的预订左连接，然后筛选出没有匹配到预订的房间，也就是可用的房间。

总结一下：用NOT IN确实能解决你当前的报错，但要注意NULL的潜在问题；更推荐用NOT EXISTS或者LEFT JOIN的写法，逻辑更健壮，性能也更优。

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