SQL关联两张表时，如何排除存在type2记录的ID？

现有表结构及数据 #

table a #

id
1
2
3
4
5

table b #

id  type
1   type1
2   type2
3   type3
4   type1
5   type1
1   type1
2   type1
5   type2

现有查询的问题 #

先执行左关联查询：

select a.id, b.type from 
table a left join table b on
a.id = b.id

针对id=5，查询结果是：

id    type 
5     type1
5     type2

如果添加where b.type = 'type1'过滤条件，语句变成：

select a.id, b.type from 
table a left join table b on
a.id = b.id where b.type = 'type1'

此时id=5的结果为：

id    type 
5     type1

但实际需求是排除所有在table b中至少有一条type2记录的ID——也就是说id=5应该返回空结果。之前尝试用exists、contains没得到预期效果，下面提供几种可行的解决方法。

解决方案 #

方法一：用NOT EXISTS子查询过滤 #

核心逻辑是先找出所有在table b中存在type2的ID，然后在主查询里排除这些ID：

select a.id, b.type
from table a
left join table b on a.id = b.id
where b.type = 'type1'
and not exists (
    select 1
    from table b as b2
    where b2.id = a.id
    and b2.type = 'type2'
)

方法二：用NOT IN筛选符合条件的ID #

先提取出table b中没有type2记录的ID集合，再关联查询：

select a.id, b.type
from table a
left join table b on a.id = b.id
where a.id not in (
    select distinct id
    from table b
    where type = 'type2'
)
and b.type = 'type1'

方法三：用LEFT JOIN + IS NULL判断 #

通过左关联type2的记录，筛选出没有匹配上的ID（即不存在type2的ID）：

select a.id, b.type
from table a
left join table b on a.id = b.id
left join table b as b2 on a.id = b2.id and b2.type = 'type2'
where b.type = 'type1'
and b2.id is null

这三种方法的核心都是先识别出所有存在type2记录的ID，再将这些ID从最终结果中彻底排除，满足“只要某个ID在table b中有type2记录，就不返回该ID的任何结果”的需求。

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