有a , b两个表,均有 name age 两个字段,现要在b表中,根据name,age 查询出在a表没有的记录,求教sql如何写,谢谢。 如下: Select * from b where not exists(select * from a where a.name=b.name and a.age=b.age)
第二种方法用左链接也可以: Select * from b left join a on b.name=a.name and b.age=a.age where a.name is null
Select b.* from b left join a on b.name=a.name and b.age=a.age where isnull(a.name) or Select b.* from a right join b on b.name=a.name and b.age=a.age where isnull(a.name)
Select b.* from b left join a on b.name=a.name and b.age=a.age where isnull(a.name) or Select b.* from a right join b on b.name=a.name and b.age=a.age where isnull(a.name)
Select * from b where not exists(select * from a where a.name=b.name and a.age=b.age)
相信上述答案中,没有不对的吧。 我给出一个: select name,age from (Select a.name as aname,b.name as name,b.age as age from b left join a on b.name=a.name and b.age=a.age ) table1 where aname is null;
你可以参考下面的例子
http://blog.csdn.net/fcoolx/archive/2008/05/21/2467179.aspx
如下:
Select * from b where not exists(select * from a where a.name=b.name and a.age=b.age)
第二种方法用左链接也可以:
Select * from b left join a on b.name=a.name and b.age=a.age where a.name is null
where isnull(a.name)
or
Select b.* from a right join b on b.name=a.name and b.age=a.age
where isnull(a.name)
where isnull(a.name)
or
Select b.* from a right join b on b.name=a.name and b.age=a.age
where isnull(a.name)
我给出一个:
select name,age from (Select a.name as aname,b.name as name,b.age as age from b left join a on b.name=a.name and b.age=a.age ) table1 where aname is null;