我有两个表,数据字段相同,但内容不同,例如表A(ID,NAME,DEPT) 表B(ID,NAME,DEPT)
我想通过ID作为联合关键字,在A表内删除“A表内存在而在B表内不存在的ID”
在B表内“删除B表内存在A表内不存在的ID”
涉及两个表的对比,除了能用in 之外的方法吗 我目前的实现方法如下
delete from A where ID in(
select ID from A a where not exists (select * from B b where a.ID=b.ID )
问题是当数据量比较大时效率会低,有别的解决方法吗?据说in效率很低这里数据量最大大概是1万多吧
我想通过ID作为联合关键字,在A表内删除“A表内存在而在B表内不存在的ID”
在B表内“删除B表内存在A表内不存在的ID”
涉及两个表的对比,除了能用in 之外的方法吗 我目前的实现方法如下
delete from A where ID in(
select ID from A a where not exists (select * from B b where a.ID=b.ID )
问题是当数据量比较大时效率会低,有别的解决方法吗?据说in效率很低这里数据量最大大概是1万多吧
delete from b where not exists(select 1 from a where a.id=b.id)
不会造成效率低下一说.少用一个exist或者in既可以了.delete from a where id in (select id from B)
delete from B where id in (select id from A)
delete from a where id not in (select distinct id from B)
delete from B where id not in (select distinct id from A)主键为一个或多个字段可用这个
delete from a where not exists(select 1 from b where a.id=b.id and...)
delete from b where not exists(select 1 from a where a.id=b.id and...) 1W数据 很快就搞定了
DELETE FROM B LEFT JOIN A ON A.ID = B.ID WHERE A.ID = NULL
where not exists (select * from B b where a.ID=b.ID )
delete a from A
where not exists (select * from B b where a.ID=b.ID ) 应该都都很快执行完吧.