Select A.* From A Left Join B On (A.id=B.id and A.name=B.name) Where isNull(B.name,'') = ''
select * from 表A a where not exists ( select * from 表B b where A.id=B.id and A.name=B.name )
select * from A where not exists(select 1 from B where id=A.id and name=A.name)
select * from A where checksum(id,name) not in(select checksum(id,name) from B)
如果你是SQL2005--创建临时表1 CREATE TABLE #Test1 ( id int, name varchar(20), re varchar(50) ); --创建临时表2 CREATE TABLE #Test2 ( id int, name varchar(20) ); --插入测试数据 INSERT INTO #Test1 SELECT 1,'a','58' UNION ALL SELECT 2,'b','52' UNION ALL SELECT 3,'c','23' UNION ALL SELECT 4,'d','44' UNION ALL SELECT 5,'e','24' UNION ALL SELECT 6,'f ','45'; GOINSERT INTO #Test2 SELECT 1,'a' UNION ALL SELECT 2,'b'; GO--查询语句 SELECT id,name FROM #Test1 EXCEPT SELECT id,name FROM #Test2; GO--结果 id name ----------- -------------------- 3 c 4 d 5 e 6 f (4 行受影响)希望对你有用
select * from 表A a where not exists ( select 1 from 表B b where A.id=B.id and A.name=B.name )
Select A.* From A Left Join B On A.id=B.id and A.name=B.name Where B.id is null
Select A.* From A Left Join B On (A.id=B.id and A.name=B.name) Where B.id is null
select * from A where(cast(id as varchar)+name) not in(select (cast(id as varchar)+name) from B)
create table A(id int,name varchar(50),re varchar(100)) create table B(id int,name varchar(50))insert b select 1,'a' union all select 2,'b'insert A select 1,'a','58' union all select 2,'b','52' union all select 3,'c','23' union all select 4,'c','44' union all select 5,'d','24' union all select 6,'d','45'select * from A where(cast(id as varchar)+name) not in(select (cast(id as varchar)+name) from B)
From A Left Join B On (A.id=B.id and A.name=B.name)
Where isNull(B.name,'') = ''
from 表A a
where not exists (
select *
from 表B b
where A.id=B.id and A.name=B.name )
where not exists(select 1 from B where id=A.id and name=A.name)
CREATE TABLE #Test1
(
id int,
name varchar(20),
re varchar(50)
);
--创建临时表2
CREATE TABLE #Test2
(
id int,
name varchar(20)
);
--插入测试数据
INSERT INTO #Test1
SELECT 1,'a','58' UNION ALL
SELECT 2,'b','52' UNION ALL
SELECT 3,'c','23' UNION ALL
SELECT 4,'d','44' UNION ALL
SELECT 5,'e','24' UNION ALL
SELECT 6,'f ','45';
GOINSERT INTO #Test2
SELECT 1,'a' UNION ALL
SELECT 2,'b';
GO--查询语句
SELECT id,name
FROM #Test1
EXCEPT
SELECT id,name
FROM #Test2;
GO--结果
id name
----------- --------------------
3 c
4 d
5 e
6 f (4 行受影响)希望对你有用
Select A.*
From A Left Join B On A.id=B.id and A.name=B.name
Where B.id is null
From A Left Join B
On (A.id=B.id and A.name=B.name)
Where B.id is null
select * from A where(cast(id as varchar)+name) not in(select (cast(id as varchar)+name) from B)
create table A(id int,name varchar(50),re varchar(100))
create table B(id int,name varchar(50))insert b
select 1,'a'
union all
select 2,'b'insert A
select 1,'a','58'
union all
select 2,'b','52'
union all
select 3,'c','23'
union all
select 4,'c','44'
union all
select 5,'d','24'
union all
select 6,'d','45'select * from A where(cast(id as varchar)+name) not in(select (cast(id as varchar)+name) from B)