select A.aid from A
where not exists(select 1 from B where A.aid=B.aid)
select A.aid from A
where a.aid not in (select B.aid from B where b.aid is not null)
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-18 13:14:00
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([Aid] int)
insert [A]
select 1001 union all
select 1002 union all
select 1003 union all
select 1004 union all
select 1005
--> 测试数据[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([Aid] int)
insert [B]
select 1001 union all
select 1004
--------------生成数据--------------------------
select * from [A] WHERE aid NOT IN (SELECT aid FROM b)
SELECT a.*
FROM a LEFT JOIN b ON a.aid=b.aid
WHERE b.aid IS NULL
SELECT * FROM a WHERE not EXISTS (SELECT 1 FROM b WHERE a.aid=b.aid)
----------------结果----------------------------
/*
Aid
-----------
1002
1003
1005
(3 row(s) affected)
Aid
-----------
1002
1003
1005
(3 row(s) affected)
Aid
-----------
1002
1003
1005
*/
