查询a表中有但b表中没有的数据,求sql?
表a
id name
1 a
2 b
3 c
4 d
表b
id a_id
1 1
2 3
-----------
期望结果
2 b
4 d
-----------
实际上b表中的a_id是和a表中的id对应。
sql: select * from a where id not in (select a.id from a join b on a.id=a_id);
有简单点的写法吗?
[解决办法]
select * from a where id not in (select a_id from b)
[解决办法]
select a.* from a left join b on a.id=b.id where b.id is null
[解决办法]
select *from awhere not exists (select 1 from b where id=a.id)