也许简单,也许复杂(求一算法)

[abysm]

我有一个问题,就是:有表a与表b.里面都有一个共同的字段"业务号"现在的要求时,检索所有表a中存在的记录,但表b不存在的.(以业务号来判断),同时检索所有表b中有的,但表a不存在的.要求是要考虑到这二个表中的记录可能有十几万条或更多.什么算法最好

[vokeyliu]

我就知道一种
select * from a where 业务号 not in (select 业务号 from b)
select * from b where 业务号 not in (select 业务号 from a)

[LAIYANGPJ]

1.X1=SECOND()
SELE A.* FROM AB1 A,AB2 B WHERE A.业务号=B.业务号 INTO DBF AA1
SELE B.* FROM AB1 A,AB2 B WHERE A.业务号=B.业务号 INTO DBF AA2
X2=SECOND()
?X2-X1
耗时:13.922秒
X1=SECOND()
select * from aB1 where 业务号 not in (select 业务号 from Ab2) INTO DBF AA1
select * from Ab2 where 业务号 not in (select 业务号 from aB1) INTO DBF AA2
X2=SECOND()
?X2-X1
耗时:9.468秒
X1=SECOND()
SELE A.* FROM AB1 A LEFT JOIN AB2 B ON A.业务号=B.业务号 INTO DBF AA1
SELE A.* FROM AB2 A LEFT JOIN AB1 B ON A.业务号=B.业务号 INTO DBF AA2
X2=SECOND()
?X2-X1
耗时:9.016秒

[LAIYANGPJ]

以上测试是AB1与AB2各有十万条记录.

[LAIYANGPJ]

以上
X1=SECOND()
SELE A.* FROM AB1 A LEFT JOIN AB2 B ON A.业务号=B.业务号 INTO DBF AA1
SELE A.* FROM AB2 A LEFT JOIN AB1 B ON A.业务号=B.业务号 INTO DBF AA2
X2=SECOND()
?X2-X1
耗时:9.016秒
应改为:
X1=SECOND()
SELE A.* FROM AB1 A LEFT JOIN AB2 B ON A.业务号=B.业务号 where !empty(b.业务号) INTO DBF AA1
SELE A.* FROM AB2 A LEFT JOIN AB1 B ON A.业务号=B.业务号 where !empty(b.业务号) INTO DBF AA1
X2=SECOND()
?X2-X1
耗时:9.453秒
4.X1=SECOND()
SELE A.* FROM AB1 A full JOIN AB2 B ON A.业务号=B.业务号 where !empty(b.业务号).AND.!empty(a.业务号) INTO DBF AA1
SELE A.* FROM AB2 A full JOIN AB1 B ON A.业务号=B.业务号 where !empty(b.业务号).AND.!empty(a.业务号) INTO DBF AA1
X2=SECOND()
?X2-X1
耗时:9.594秒

[abysm]

谢谢小丑对该问题的关心,但你没有完全理解我的意思.其实 vokeyliu的二条语句已经为我解决问题了.

[abysm]

哦,不好意思.看错了.下次有问题的时候,给你补分.