delete from tb1 t1 where t1.pk_id not in (select t2.fk_id from tb2 t2 );
--使用not exists(主表main,明细表detail) DELETE FROM detail d WHERE NOT EXISTS (SELECT 1 FROM main m WHERE m.id = d.id);
一楼和二楼都可以实现这个功能,但从效率的角度讲,二楼比一楼高。以后写SQL尽量少用in
create table manager as( select '7788' manager_id,'sales' manager_dept from dual union all select '5566','research' from dual union all select '8899','accounting' from dual union all select '1122','operations' from dual) create table employee as( select '1001' employee_id,'1122' manager_id,'scott' ename,500 sal from dual union all select '10012','8899','james',1000 from dual union all select '10010','3322','john',120 from dual union all select '1003','5566','tom',1500 from dual union all select '10015','1100','smith',1250 from dual) -- 1.找出manager表中管理的员工(在employee表中): select * from employee e where exists (select 1 from manager m where m.manager_id=e.manager_id);
EMPLOYEE_ID MANAGER_ID ENAME SAL ----------- ---------- ----- ---------- 1003 5566 tom 1500 10012 8899 james 1000 1001 1122 scott 5002.删除employee表中没有对应manager表中manager_id的数据 既employee表中manager_id为1100和3322的数据: SQL> select * from employee;EMPLOYEE_ID MANAGER_ID ENAME SAL ----------- ---------- ----- ---------- 1001 1122 scott 500 10012 8899 james 1000 10010 3322 john 120 1003 5566 tom 1500 10015 1100 smith 1250delete from employee e where not exists (select 1 from manager m where m.manager_id=e.manager_id);
2 rows deletedSQL> select * from employee;EMPLOYEE_ID MANAGER_ID ENAME SAL ----------- ---------- ----- ---------- 1001 1122 scott 500 10012 8899 james 1000 1003 5566 tom 1500很多oracle优化教材都告诉我们:使用exists而不是inin用于检查一个值是否包含在列表中,既检查实际值,进行值的比较;而exists只检查行的存在性,返回一个bool值(true or false); 特别在子查询中,exists提供的性能要比in提供的性能要好!
where t1.pk_id not in (select t2.fk_id from tb2 t2 );
DELETE FROM detail d
WHERE NOT EXISTS (SELECT 1 FROM main m WHERE m.id = d.id);
create table manager as(
select '7788' manager_id,'sales' manager_dept from dual union all
select '5566','research' from dual union all
select '8899','accounting' from dual union all
select '1122','operations' from dual)
create table employee as(
select '1001' employee_id,'1122' manager_id,'scott' ename,500 sal from dual union all
select '10012','8899','james',1000 from dual union all
select '10010','3322','john',120 from dual union all
select '1003','5566','tom',1500 from dual union all
select '10015','1100','smith',1250 from dual)
--
1.找出manager表中管理的员工(在employee表中):
select * from employee e
where exists
(select 1 from manager m where m.manager_id=e.manager_id);
EMPLOYEE_ID MANAGER_ID ENAME SAL
----------- ---------- ----- ----------
1003 5566 tom 1500
10012 8899 james 1000
1001 1122 scott 5002.删除employee表中没有对应manager表中manager_id的数据
既employee表中manager_id为1100和3322的数据:
SQL> select * from employee;EMPLOYEE_ID MANAGER_ID ENAME SAL
----------- ---------- ----- ----------
1001 1122 scott 500
10012 8899 james 1000
10010 3322 john 120
1003 5566 tom 1500
10015 1100 smith 1250delete from employee e
where not exists
(select 1 from manager m where m.manager_id=e.manager_id);
2 rows deletedSQL> select * from employee;EMPLOYEE_ID MANAGER_ID ENAME SAL
----------- ---------- ----- ----------
1001 1122 scott 500
10012 8899 james 1000
1003 5566 tom 1500很多oracle优化教材都告诉我们:使用exists而不是inin用于检查一个值是否包含在列表中,既检查实际值,进行值的比较;而exists只检查行的存在性,返回一个bool值(true or false);
特别在子查询中,exists提供的性能要比in提供的性能要好!