两个存放有千万级数据的表
A(agent_id,name)和B(agent_id,xxx),A表的agent_id是主键,B表的agent_id是外键,关联到A表,
写条sql,从A表取出不存在于B表的数据求高效的sql写法
A(agent_id,name)和B(agent_id,xxx),A表的agent_id是主键,B表的agent_id是外键,关联到A表,
写条sql,从A表取出不存在于B表的数据求高效的sql写法
select * from a
where agent_id not in (select agent_id from b);
select * from a
where not exists (select 1 from b where agent_id=a.agent_id);
select a.* from a, b
where a.agent_id = b.agent_id (+)
and b.rowid is null;
大部分情况下,left join效率高。
where not exists(select 1 from b
where agent_id=a.agent_id)
select a.* from a, b
where a.agent_id = b.agent_id (+)
and b.rowid is null;和select * from a
where not exists(select 1 from b
where agent_id=a.agent_id)
那个高一些
where a.agent_id = b.agent_id (+)
and b.agent_id is null;
where a.agent_id != b.agent_id 最简单了