两个表作比较sql查询语句

[cqhweb]

已知A表与B表中的数据不对称, 现要作比较。求查询A表不存在B表中的数据？

u_name 姓名
u_pcode 身份证号码(主键)


A表数据 B表数据

u_name u_pcode u_name u_pcode

admin 63212419841026401X test 610324198310230053
张三 430626198306130008 张三 430626198306130009
李四 511025198103152192 李四 511025198103152192
王五 61050219800920721X 王五 61050219800920721X
赵六 430381198209243670 霍七 632126198505152718
陈九 .................. ........................



提示:姓名有可能相同，但是身份证号码不同。

查询结果如下

test 610324198310230053
张三 430626198306130009
霍七 632126198505152718
........................

[dawugui]

select a.* from a where not exists(select 1 from b where u_name = a.u_name and u_pcode = a.u_pcode)

[dawugui]

--a表有,b表没有
select a.* from a where not exists(select 1 from b where u_name = a.u_name and u_pcode = a.u_pcode)

--b表有,a表没有
select B.* from b where not exists(select 1 from a where u_name = B.u_name and u_pcode = B.u_pcode)

[dawugui]

create table a (u_name varchar(10) , u_pcode varchar(18))
create table b (u_name varchar(10) , u_pcode varchar(18))
insert into a values('张三', '430626198306130008') 
insert into a values('李四', '511025198103152192') 
insert into a values('王五', '61050219800920721X') 
insert into a values('赵六', '430381198209243670') 
insert into b values('张三', '430626198306130009')   
insert into b values('李四', '511025198103152192')   
insert into b values('王五', '61050219800920721X')   
insert into b values('霍七', '632126198505152718')
go

--a表有,b表没有
select a.* from a where not exists(select 1 from b where u_name = a.u_name and u_pcode = a.u_pcode)
/*
u_name     u_pcode            
---------- ------------------ 
张三         430626198306130008
赵六         430381198209243670

（所影响的行数为 2 行）
*/


--b表有,a表没有
select B.* from b where not exists(select 1 from a where u_name = B.u_name and u_pcode = B.u_pcode)
/*
u_name     u_pcode            
---------- ------------------ 
张三         430626198306130009
霍七         632126198505152718

（所影响的行数为 2 行）
*/


drop table a , b 

[老黎]

select * 
from B t
where not exists(select 1 from A where u_name = t.u_name and u_pcode = t.u_pcode)