如何从表A里抽取表B里没有的纪录 有共同的key字段

[bubu198]

请给出例子

[beckhambobo]

select * from a where not exists(select 1 from b where a.id=b.id)

[LGQDUCKY]

.keycol=b.keycol(+) means left join.
a.keycol(+)=b.keycol means right join.
用这样的方法效率好点：

[whatwhynot]

select a.* from a,b where a.keycol=b.keycol(+);
这是外连接，意思就是扩展b表！！

[seutxf77]

Thanks !!!

[seutxf77]

SQL>select * from a,b where a.key not in (select b.key from b)

[leecooper0918]

Do not use NOT IN statements, because it's efficiency is very very low.

Try this:
select a.* from a,b where a.keycol=b.keycol(+) and b.keycol is null

[seutxf77]

why???please tell me!!Thanks....
what mean "b.keycol(+)"??????I have never saw it!!

[leecooper0918]

a.keycol=b.keycol(+) means left join.
a.keycol(+)=b.keycol means right join.

if you are using oracle 9i now, you can use left(right) join in
your sql statements,because oracle 9i supports SQL99 standard.

BTW: you may read some oracle sql/plsql ariticles and documentation
to learn more about it.

[qiansl]

左（右）连接其实也没什么的，写一个就知道了

[chanet]

可用使用 minus 关键字.
如果两个表的结构是相同的话:
select * from a minus select * from b; <- (显示在 A 表里,B 表没有的记录)

也可以指定字段:
select field1,field2 from a minus select field3,field4 from b

注意: 只有字段类型相同才可以进行比较.

[yangguang1219]

select * from a where not exists(select 1 from b where a.id=b.id)
这里的１代表什么啊

[nicolas1999king]

agree on leecooper0918(爱一个人好难) .

[leecooper0918]

人家问的是比较记录 搞什么字段相同来比较
---------------------------------------
faint, 搞不懂你在说什么?


请看清楚楼主的问题再发言

如何从表A里抽取表B里没有的纪录 有共同的key字段
----------------------------------------------

[aiu]

什么啊 人家问的是比较记录 搞什么字段相同来比较 还搞什么外链接和内连接 哈。。