请教：两张表一张表中的记录是另一表的子集，怎么查询出该表中非另一表子集的记录

[Dream_1986]

现在有两张表，一张表有2826条记录称为表a，另外一张表，有7000多条记录称为表b，现在表a中的记录都是表b中的，现在要求将表b中不包含a中的记录查询出来，应该怎么做？？？？谢谢！！！

[xuam]

select * from b where not exist (select * from a)

[SQL77]

SELECT * FROM B WHERE NOT EIXSTS(SELECT 1 FROM A WHERE 关联字段)
2005用EXCEPT

[SQL77]

SELECT * FROM B WHERE ID NOT IN (SELECT ID FROM A )
ID唯一

[--小F--]

select  *  from  b  where not exist (select  * from  a where id=b.id)

[--小F--]

select  *  from  b  where not exists (select  * from  a where id=b.id)

[xuam]

select * from b where not exists (select * from a)

[华夏小卒]

if object_id('num')is not null drop table num
go
create table num( n int ) 
insert into num 
select 1 union 
select 2 union 
select 3 union 
select 4 union 
select 5 union 
select 6 union 
select 7 union 
select 8 union 
select 9 


if object_id('num1')is not null drop table num1
go
create table num1( n int ) 
insert into num1 
select 1 union 
select 2 union  
select 7 union 
select 8 union 
select 9 

select * from num  
except 
select * from num1 


n
-----------
3
4
5
6

(4 行受影响)

[Flyingdragon168]

如何判断A表在B表中，应当有个一个或多个关键栏位吧。
SELECT * FROM b WHERE ColumX not IN (SLEECT ColumnX FROM A)
其中ColumnX 表示关键栏位:
如果是一个栏位，则Column1
如果多个，可以IsNull(Colomn_No1,'')+IsNull(Column_No2,'')等。

[navy887]

Select  *  from  b  Where not exist (select * from a)
--或
Select  *  from  b  Where id not in (select id from a)

[Dream_1986]

其实，a表中还有部分b表中不能完全匹配的记录。。

[SIMENYU]

--SQL 2000
select * from b where id not in (select id from a)
--or
select * from b where not exists (select 1 from a where a.id=b.id) --id是唯一列;

--SQL 2005,若表结构相同,则:
select * from b
except
select * from a

[忆轩辕]

SQL2000只能用not exists判断字段（如果有一唯一字段的话，没有就全字段判断吧）
SQL2005用except