sele field from table1 a, table2 b where a.field<>b.field 这样怎么样， 是不是速度一样的 因为好象循环次数都是一样的。
用临时表来做可以显著提高速度 create table #tt ( field char(10) , flag int ) insert into #tt select field,0 from table1 update #tt set flag = 1 where field in ( select field from table2) select field from #tt where flag = 0
如果是在oracle中，可用minus select * from table1 minus select * from table2如果是sql server,用not exists. select * from table1 not exists (select * from table2 )
select temp.field1 from ( select t1.field as field,t2.field as field1 from table1 t1 left join table2 t2 on t1.field = t2.field ) as temp where temp.field1 is null
是这样的。 select temp.field from ( select t1.field as field,t2.field as field1 from table1 t1 left join table2 t2 on t1.field = t2.field ) as temp where temp.field1 is null效率如何呢？
select t1.field from table1 t1 left join table2 t2 on t1.field = t2.field where t2.field1 is null
要看的 "where" 部份有甚麼要求. 適當的使用where 就可以利用 index 來加快 sql了, 不然只是兩個表的乘積, 當然會較慢.
select field from table1 where not exists (select field from table2 where table1.field=table2.field)
这样怎么样，
是不是速度一样的
因为好象循环次数都是一样的。
create table #tt ( field char(10) , flag int ) insert into #tt select field,0 from table1 update #tt set flag = 1 where field in ( select field from table2) select field from #tt where flag = 0
select * from table1
minus
select * from table2如果是sql server,用not exists.
select * from table1
not exists
(select * from table2 )
select t1.field as field,t2.field as field1 from table1 t1
left join table2 t2 on t1.field = t2.field ) as temp
where temp.field1 is null
select temp.field from (
select t1.field as field,t2.field as field1 from table1 t1
left join table2 t2 on t1.field = t2.field ) as temp
where temp.field1 is null效率如何呢？
left join table2 t2 on t1.field = t2.field where t2.field1 is null
適當的使用where 就可以利用 index 來加快 sql了,
不然只是兩個表的乘積, 當然會較慢.