如何查询表A中的字段a和字段b的值在表B中不存在
如何查询表A中的字段ID和字段Name的值在表B中不存在
如下：
A表结构
ID Name Levels Flag
1001 Test 0 0
1001 Gust 0 0
1001 Gust 1 1
1002 Admin 0 0
1003 Test 0 0
1004 Test 0 0
1005 Gust 0 0
1006 Gust 1 1
1007 Admin 0 0
1008 Test 0 0

B表结构
ID Name SavePlace sort
1001 Test 仓库 测试
1002 Test 仓库 测试



[解决办法]

SELECT A.* FROM A 
WHERE NOT EXISTS(
   SELECT 1 FROM B  WHERE B.ID=A.ID AND B.NAME=A.NAME
)

[解决办法]

----------------------------
-- Author  :DBA_Huangzj()
-- Date    :2013-04-09 17:24:13
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--Jun 17 2011 00:54:03 
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[Name] varchar(5),[Levels] int,[Flag] int)
insert [A]
select 1001,'Test',0,0 union all
select 1001,'Gust',0,0 union all
select 1001,'Gust',1,1 union all
select 1002,'Admin',0,0 union all
select 1003,'Test',0,0 union all
select 1004,'Test',0,0 union all
select 1005,'Gust',0,0 union all
select 1006,'Gust',1,1 union all
select 1007,'Admin',0,0 union all
select 1008,'Test',0,0

--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[Name] varchar(4),[SavePlace] varchar(4),[sort] varchar(4)) 
 
insert [B]
select 1001,'Test','仓库','测试' union all
select 1002,'Test','仓库','测试'
--------------开始查询--------------------------

select * from [A]
WHERE NOT EXISTS (SELECT 1 FROM  [B]  WHERE a.id=b.id AND A.NAME=b.NAME)
----------------结果----------------------------
/* 
ID          Name  Levels      Flag
----------- ----- ----------- -----------
1001        Gust  0           0
1001        Gust  1           1
1002        Admin 0           0
1003        Test  0           0
1004        Test  0           0
1005        Gust  0           0
1006        Gust  1           1
1007        Admin 0           0
1008        Test  0           0
*/

[解决办法]
if exists(select * from sysobjects where name = 'A')
drop table A
if exists(select * from sysobjects where name = 'B')
drop table B
go
create table A
(
ID varchar(10),
Name varchar(10),
Levels int,
Flag int
)
create table B
(
ID varchar(10),
Name varchar(10),
SavePlace varchar(10),
sort varchar(10)
)
go

insert into A values('1001','Test',0,0)
insert into A values('1002','Gust',0,0)
insert into A values('1003','Admin',0,0)

insert into B values('1001','Test','仓库','测试')
insert into B values('1002','Test','仓库','测试')
go

select * from A
select * from B

go
--不存在B表中的ID
select A.id from A WHERE id not in(select id from B)
--不存在B表中的Name
select A.Name from A WHERE Name not in(select Name from B)

go
--放在一起
select * from
(select A.id from A WHERE id not in(select id from B))t1,
(select A.Name from A WHERE Name not in(select Name from B))t2
[解决办法]
SELECT A.* FROM A WHERE NOT EXISTS( SELECT 1 FROM B WHERE B.ID=A.ID AND B.NAME=A.NAME)