求一两表对比查询语句

[KK_LIU]

系统环境：W2k+SQL2000
表A：Name,Age,Time
表B：Name,Tel,Age,Score,Bg,Address
表A有10万记录，表B有上百万记录，均以Name,Age两字段标识唯一记录。
现要查出表A中还没录入表B的记录，这SQL该怎么写？

[华夏小卒]

select * from A
where exists (select 1 from b where a.name<>b.name and a.age<>b.age)

[让你望见影子的墙]

select * from a
where not exists(select 1 from b where a.name=b.name and a.age=b.age)

[--小F--]

select 
  * 
from  
  a 
where 
not exists(select 1 from b where a.name=b.name and a.age=b.age)

[黄_瓜]

select * from a 
where not exists(select 1 from b where a.name=b.name and a.age=b.age)

不知道是不是最高效的

[黄_瓜]

[Quote=引用 2 楼 herowang 的回复:]
select * from a
where not exists(select 1 from b where a.name=b.name and a.age=b.age)
[/Quote]
多打几个字就跑你后面去了 O(∩_∩)O~

[jiannye]

[Quote=引用 2 楼 herowang 的回复:]
select * from a
where not exists(select 1 from b where a.name=b.name and a.age=b.age)
[/Quote]

跟一楼的没什么区别吗

[十八道胡同]

select * from a
where not exists (select 1 from b where a.name=b.name and a.age=b.age)

[guoli0813]

ding

[邹夫子]

[Quote=引用 7 楼 jack_ailly 的回复:]
SQL 2005：
SQL codeselect name,agefrom aexceptselect name,agefrom b
[/Quote]

测试了一下，好像只有七楼的可以

[flylovejings]

[Quote=引用 7 楼 jack_ailly 的回复:]
SQL 2005：
SQL codeselect name,agefrom aexceptselect name,agefrom b
[/Quote]


不理解except关键字

关注下

[邹夫子]

select * from b 
    where not exists(select 1 from a where a.name=b.name and a.age=b.age)

不要搞反了A、B的顺序

[jack_ailly]

SQL 2005：

select name,age from a
except
select name,age from b

[fanzhouqi]

select
                * 
          from a 
                left join b 
                    on a.name=b.name
          where b.name is null             

[lihan6415151528]

select 
  * 
from  
  a 
where 
not exists(select 1 from b where a.name=b.name and a.age=b.age)

[ChinaITOldMan]

select * from a
where not exists(select 1 from b where a.name=b.name and a.age=b.age)

[dawugui]

[Quote=引用楼主 kk_liu 的回复:]
系统环境：W2k+SQL2000
表A：Name,Age,Time
表B：Name,Tel,Age,Score,Bg,Address
表A有10万记录，表B有上百万记录，均以Name,Age两字段标识唯一记录。
现要查出表A中还没录入表B的记录，这SQL该怎么写？
[/Quote]
select a.* from a where not exists(select 1 from b where name = a.name and age = a.age)

[KK_LIU]

谢谢大家，可以了，虽然不知是不是最好的效率，交完差再作研究...