求两个表没有的记录,该如何处理

求两个表没有的记录
A 表 （合同基本信息表）
ID ContractNO 
1 11111 
2 22222
3 33333

B 表 （合同产品信息表）
ID ContractNO Product Supplier
1 11111 卷尺 上海 
2 11111 橡皮 北京
3 11111 卷尺 北京
4 22222 卷尺 上海
5 33333 卷尺 上海

C 表 （供货单位）
ID ContractNO Supplier
1 11111 北京 
2 33333 上海

得到的结果是

ID ContractNO Product Supplier
1 11111 卷尺 上海 
4 22222 卷尺 上海


就是求出B表中没有与C表相同合同和供货单位的记录集



[解决办法]
select * from B b where not exists(
select 0 from b,C c where b.ContractNO =C.ContractNO and b.Supplier=c.Supplier)
[解决办法]

SQL code

select * from b where id <> (select id from b inner join c on b.ContractNO=c.ContractNO and b.Supplier=c.Supplier)
[解决办法]
SQL code
select b.* from b left join c on b.ContractNO=c.ContractNO and b.Supplier=c.Supplierwhere c.ContractNO is null
[解决办法]
SQL code
create table tA(  ID int,  ContractNO varchar(5),  Product nvarchar(10),  Supplier nvarchar(10))insert into tAselect 1,'11111', '卷尺', '上海'  union allselect 2, '11111', '橡皮', '北京'  union allselect 3, '11111', '卷尺', '北京'  union allselect 4, '22222', '卷尺', '上海'  union allselect 5, '33333', '卷尺', '上海'create table tB(  ID int,  ContractNO varchar(5),  Supplier nvarchar(10))insert into tBselect 1,'11111','北京'  union allselect 2, '33333',  '上海' select * from tA a where not exists(select 1 from tB where ContractNO=a.ContractNO and Supplier=a.Supplier)
[解决办法]
SQL code
select b.* from b right join c on b.ContractNO=c.ContractNO and b.Supplier=c.Supplierwhere b.ContractNO is null
[解决办法]
SQL code
if object_ID('b') is not null   drop table bgocreate table b( id int identity(1,1), contractno varchar(10), product varchar(10), supplier varchar(10))goinsert into b (contractno,product,supplier)select '11111','卷尺','上海' union allselect '11111','橡皮','北京' union allselect '11111','卷尺','北京' union allselect '22222','卷尺','北京' union allselect '33333','卷尺','上海'goif object_id('c') is not null   drop table cgocreate table c( id int identity(1,1), contractno varchar(10), supplier varchar(10))goinsert into c (contractno,supplier)select '11111','北京' union allselect '33333','上海'goselect * from b t1 where not exists(select 1 from c where contractno=t1.contractno and Supplier=t1.Supplier)/*id          contractno product    supplier----------- ---------- ---------- ----------1           11111      卷尺         上海4           22222      卷尺         北京(2 行受影响)*/