SQL 表连接问题

create table 表A (

	店CD char,

	商品CD varchar(10)

)

create table 表B (

	店CD char,

	商品CD varchar(10)

)



insert into 表A (店CD,商品CD) values('1','aaaa')

insert into 表A (店CD,商品CD) values('2','bbbb')

insert into 表A (店CD,商品CD) values('3','vvvv')

insert into 表A (店CD,商品CD) values('4','dddd')

insert into 表A (店CD,商品CD) values('5','rrrr')

insert into 表A (店CD,商品CD) values('6','jjjj')





insert into 表B (店CD,商品CD) values('1','aaaa')

insert into 表B (店CD,商品CD) values('2','bbbb')

insert into 表B (店CD,商品CD) values('3','vvvv')



--结果

select *

from 表A a

where not exists(select 1 from 表B where 店CD=a.店CD and 商品CD=a.商品CD)



/*

店CD  商品CD       

---- ---------- 

4    dddd

5    rrrr

6    jjjj



（所影响的行数为 3 行）

*/

select * from a n where not exists(select * from b where a.店CD=店CD and a.商品CD =商品CD 

)

--同时算店CD   ，商品CD 

SELECT A.* FROM A WHERE CAST(店CD AS VARCHAR) + ',' + CAST(商品CD AS VARCHAR) NOT IN (SELECT CAST(店CD AS VARCHAR) + ',' + CAST(商品CD AS VARCHAR) FROM B)



--只算店CD

SELECT A.* FROM A WHERE 店CD NOT IN (SELECT 店CD FROM B)



--只算商品CD 

SELECT A.* FROM A WHERE 商品CD NOT IN (SELECT 商品CD FROM B)

SELECT A.* FROM A WHERE CAST(店CD AS VARCHAR) + ',' + CAST(商品CD AS VARCHAR) NOT IN (SELECT CAST(店CD AS VARCHAR) + ',' + CAST(商品CD AS VARCHAR) FROM B)