oracle delete 左连接,在Oracle 10g中使用左连接删除

表格和数据：

SQL> create table grp (id1 number null, id2 number null, id3 number null, id4 number null);

Table created.

SQL> create table my_data (id1 number null, id2 number null, id3 number null, id4 number null);

Table created.

SQL> insert into grp values (1, 2, 3, 4);

1 row created.

SQL> insert into grp values (10, 20, 30, 40);

1 row created.

SQL> insert into grp values (1, 2, 30, 40);

1 row created.

SQL> insert into my_data values (1, 2, 3, 4);

1 row created.

SQL> commit;

Commit complete.

使用in.注意如果子查询中的ID可以为空,请勿使用.不在null不会返回true.

SQL> delete grp where (id1, id2, id3, id4) not in (select id1, id2, id3, id4 from my_data);

2 rows deleted.

SQL> select * from grp;

ID1 ID2 ID3 ID4

---------- ---------- ---------- ----------

1 2 3 4

使用存在

SQL> rollback;

Rollback complete.

SQL> delete grp where not exists (select * from my_data where grp.id1 = my_data.id1 and grp.id2 = my_data.id2 and grp.id3 = my_data.id3 and grp.id4 = my_data.id4);

2 rows deleted.

SQL> select * from grp;

ID1 ID2 ID3 ID4

---------- ---------- ---------- ----------

1 2 3 4

SQL>