select * from tb t where not exists(select 1 from ta where m=t.m and n=t.n)
select b.* from b where b.id not in (select a.id from a)
select * from B where M not in (select M from A)
select * from b except select * from a
当不确定哪个表多哪个表少时:-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-19 15:45:23 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -------------------------------------------------------------------------- --> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL DROP TABLE [a] GO CREATE TABLE [a]([M] INT,[N] INT) INSERT [a] SELECT 1,100 GO --SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL DROP TABLE [b] GO CREATE TABLE [b]([M] INT,[N] INT) INSERT [b] SELECT 1,100 UNION ALL SELECT 2,200 UNION ALL SELECT 3,300 GO --SELECT * FROM [b]-->SQL查询如下: select 'a' 表名,* from a except select 'a',* from b union all select 'b' 表名,* from b except select 'b',* from a /* 表名 M N ---- ----------- ----------- b 2 200 b 3 300(2 行受影响) */
select b.* from b where not exists (select a.* from a where a.m=b.m and a.n=b.n)
from tb t
where not exists(select 1 from ta where m=t.m and n=t.n)
-- Author : htl258(Tony)
-- Date : 2010-04-19 15:45:23
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([M] INT,[N] INT)
INSERT [a]
SELECT 1,100
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([M] INT,[N] INT)
INSERT [b]
SELECT 1,100 UNION ALL
SELECT 2,200 UNION ALL
SELECT 3,300
GO
--SELECT * FROM [b]-->SQL查询如下:
select 'a' 表名,* from a except select 'a',* from b
union all
select 'b' 表名,* from b except select 'b',* from a
/*
表名 M N
---- ----------- -----------
b 2 200
b 3 300(2 行受影响)
*/
from b
where not exists (select a.* from a where a.m=b.m and a.n=b.n)