关于Oracle里exists可以和in等价改写

Question

背景：
create table test as select * from bda_objects;
insert into test select * from test；
此时test表，有2份数据是重复的，需求：
把所有重复的数据删除，只保留一份数据。

用exists删：
delete test a
where exists(select b.object_id
from test b
where a.object_id = b.object_id
and a.rowid < b.rowid);——成功

等价改成用in删：
delete test a
where a.object_id in (select b.object_id
from test b
where a.object_id = b.object_id
and a.rowid < b.rowid);——成功——为啥这样写就能成功？

改成这样的：
delete test a
where a.rowid in (select b.rowid
from test b
where a.object_id = b.object_id
and a.rowid < b.rowid);——失败，删除0条——不知道为啥？

Answer 1

你最后这个sql的确是删不掉数据的,"a.rowid in (select b.rowid ...) "表示 要找出 "a.rowid=b.rowid"的数据,但是这个in里面,又有这个条件"a.rowid < b.rowid",既等于又小于,这两个条件是不可能同时成立的,因此找不到数据。
另外,你第一个sql

delete test a
where exists(select b.object_id
from test b
where a.object_id = b.object_id
and a.rowid < b.rowid);

其实子查询里select 的字段可以为任何东西,你select 1都行,比如

delete test a
where exists(select 1
from test b
where a.object_id = b.object_id
and a.rowid < b.rowid);

因为它的重点不在于select了哪个字段,而是在于后面的这两个条件 object_id 和 rowid,
可以发现,object_id是用的相等,所以object_id可以用in找到;而rowid是小于,当然就不能用in找到了