select * from a where key not in (select distinct key from b);
select * from a where not exists(select 1 from b where a.id=b.id)
用Minusselect * from employee where name like 'A%' or name like 'B' minus select * from employee where name like 'A%'; /
select * from a where a.id in (select id form a minus select id from b)
select * from a where key not in (select distinct key from b);
select id from a minus select id from a,b where a.id=b.id;
最快的方法是使用外连接: select a.col1,a.col2,... from t1,t2 where t1.keycol=t2.keycol(+) and t2.keycol is null 不要使用Not in,会做两次全表扫描(可以查看查询计划) 也不要使用minus,大数据量时效率也很低. 用exist子句可以.
minus
select * from employee where name like 'A%';
/
minus
select id from a,b where a.id=b.id;
select a.col1,a.col2,... from t1,t2 where t1.keycol=t2.keycol(+)
and t2.keycol is null 不要使用Not in,会做两次全表扫描(可以查看查询计划)
也不要使用minus,大数据量时效率也很低. 用exist子句可以.