SQL多列子查询IN语句报错：非布尔类型表达式问题排查

你编写的SQL语句使用了多列组合IN子查询的语法：

select * from [Monitoring].[dbo].[MaintTaskMonitor]  
where (JobStartDate,TaskType) in (
select max(JobStartDate),TaskType  from [Monitoring].[dbo].[MaintTaskMonitor]  where systemname='Dido'
group by TaskType) 

执行后触发的错误：

An expression of non-boolean type specified in a context where a condition is expected, near ','.

错误原因 #

SQL Server 不支持(列1, 列2) IN (子查询)这种多列匹配的IN语法——这种写法常见于MySQL、PostgreSQL等数据库，但在SQL Server中，IN子句仅支持针对单个列进行匹配，多列组合的写法会被解析为无效表达式，因此报错提示“在需要布尔条件的位置指定了非布尔类型的表达式”。

修正方案 #

可以通过以下两种方式实现需求：

方法1：使用INNER JOIN关联子查询

select m.* 
from [Monitoring].[dbo].[MaintTaskMonitor] m
inner join (
    select max(JobStartDate) as MaxJobStartDate, TaskType 
    from [Monitoring].[dbo].[MaintTaskMonitor] 
    where systemname='Dido'
    group by TaskType
) t on m.JobStartDate = t.MaxJobStartDate and m.TaskType = t.TaskType

方法2：用AND结合子查询分别匹配列

select * 
from [Monitoring].[dbo].[MaintTaskMonitor]  
where TaskType in (select TaskType from [Monitoring].[dbo].[MaintTaskMonitor] where systemname='Dido')
and JobStartDate = (select max(JobStartDate) from [Monitoring].[dbo].[MaintTaskMonitor] where systemname='Dido' and TaskType = MaintTaskMonitor.TaskType)

内容的提问来源于stack exchange，提问作者a.Paryab