select * from bbb where name not in (select name from aaa) 或者 select * from bbb where not exists(select * from aaa where name=bbb.name)
如果表aaa,和bbb,他们的结构是一样的select * from bbb minus select * from aaa;或者select * from bbb,(select name from bbb minus select name from aaa) a where bbb.name=a.name;
select * from bbb,(select name from bbb minus select name from aaa) a where bbb.name=a.name; 这句改一下 select bbb.* from bbb,(select name from bbb minus select name from aaa) a where bbb.name=a.name;
Select * From Bbbb Where Not Exists (Select 'X' From Aaa Where Aaa.Name = Bbb.Name)
这个速度应该会快一点吧 SELECT * FROM BBB WHERE NOT EXISTS (SELECT 'X' FROM AAA WHERE AAA.NAME = BBB.NAME)
select * from bbb b where not exists(select 1 from aaa a where a.name=b.name)
楼主是江洋大盗?应该是最优的SQL了:select b.* from aa a,bb b where a.name(+)=b.name and a.name is null
所有的SQL 好象都对,您先测试一下!!
select bbb.* from bbb,aaa where bbb.name=aaa.name(+) and aaa.name is null
你们为什么要and name is null, name不是索引吗?!
好像都不对吧,楼主说两表都有name主索引,但楼上给出的都是bbb表记录中name字段不在aaa表中name字段的记录,楼主应该是要取出整条记录都不在aaa表中的记录,是否应该是,select * from bbb m where m.* not in(select *from aaa)
--这句应该可以的 select name from bbb where not exists(select name from aaa)
--应该可以得到你想要的结果select name from bbb where not exists(select name from aaa)
select * from bbb where name not in(select name from aaa);
或者
select * from bbb where not exists(select * from aaa where name=bbb.name)
minus
select * from aaa;或者select * from bbb,(select name from bbb minus select name from aaa) a
where bbb.name=a.name;
where bbb.name=a.name;
这句改一下
select bbb.* from bbb,(select name from bbb minus select name from aaa) a
where bbb.name=a.name;
From Bbbb
Where Not Exists (Select 'X' From Aaa Where Aaa.Name = Bbb.Name)
SELECT *
FROM BBB
WHERE NOT EXISTS (SELECT 'X' FROM AAA WHERE AAA.NAME = BBB.NAME)
where not exists(select 1 from aaa a where a.name=b.name)
where bbb.name=aaa.name(+)
and aaa.name is null
name不是索引吗?!
where m.* not in(select *from aaa)
--这句应该可以的
select name from bbb where not exists(select name from aaa)