假设有2个表,分别是 aaa 表(id,otherdata) 和 bbb(id,otherdata) 表 两个表结构完全一致(有主键id,other代表其他字段 有120个吧),我想实现的目的 挑出来aaa表中存在的记录 而bbb表中不存在的记录 从aaa表中删除,aaa表中存在delete触发器,同时会将删除的记录插入另外一个表中。aaa表中最多有17000条数据delete from aaa where exists(select id from aaa except select id from bbb)delete from aaa where id in(select id from aaa a where not exists (select * from bbb b where a.id=b.id))delete from aaa where id in(select id from except select id from bbb)他们是否等价?那个效率高一点?
-->相当于:
if exists (select id from aaa except select id from bbb)
begin
delete from aaa
end
delete from aaa where id in(select id from aaa a where not exists (select * from bbb b where a.id=b.id))
-->相当于:
delete from aaa as t where not exists (select 1 from bbb where id=t.id)
第三条语句错误。
delete from aaa as t where not exists (select 1 from bbb where id=t.id)