sql 求两表交集两种方法

sql 求两表交集两种方法

dept表

id deptid
1 20
2 20
3 20

user表

id userid
1 33
2 34
3 34

方法一

select distinct userid
from user u
where id in (select id from dept where deptid=20)
and not exists (select 1 from user where id in (select id from dept deptid=20) and userid<>u.userid)

方法二 join

select distinct userid
from user u inner join dept d on u.id=d.id
where d.deptid=20
and not exists (select 1 from user inner join dept on user.id=dept.id where userid<>u.userid)