/* -- Author:Flystone -- Date:2008-05-15 -- Version:V1.001 */-- Test Data: Ta If object_id('Ta') is not null Drop table Ta Go Create table Ta(id int) Go Insert into Ta select 1 union all select 2 union all select 3 union all select 4 Go -- Test Data: Tb If object_id('Tb') is not null Drop table Tb Go Create table Tb(id int) Go Insert into Tb select 1 union all select 2 Go --Start Select * from Ta a where not exists(select 1 from tb where a.id = id) --Result: /*id ----------- 3 4(所影响的行数为 2 行) */ --End
select * from 1表 a where not eixsts(select 1 from 2表 where id = a.id)
Select * from 表1 a where not exists(select 1 from 表2 where a.id = id)
create table #tab1(id int) insert into #tab1(id) values(1) insert into #tab1(id) values(2) insert into #tab1(id) values(3) insert into #tab1(id) values(4) insert into #tab1(id) values(5) insert into #tab1(id) values(6) create table #tab2(id int) insert into #tab2(id) values(1) insert into #tab2(id) values(3) insert into #tab2(id) values(4)select id from #tab1 except select id from #tab2
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(id int)
Go
Insert into Ta
select 1 union all
select 2 union all
select 3 union all
select 4
Go
-- Test Data: Tb
If object_id('Tb') is not null
Drop table Tb
Go
Create table Tb(id int)
Go
Insert into Tb
select 1 union all
select 2
Go
--Start
Select *
from Ta a
where not exists(select 1 from tb where a.id = id)
--Result:
/*id
-----------
3
4(所影响的行数为 2 行)
*/
--End
select * from 1表 a
where not eixsts(select 1 from 2表 where id = a.id)
from 表1 a
where not exists(select 1 from 表2 where a.id = id)
insert into #tab1(id) values(1)
insert into #tab1(id) values(2)
insert into #tab1(id) values(3)
insert into #tab1(id) values(4)
insert into #tab1(id) values(5)
insert into #tab1(id) values(6)
create table #tab2(id int)
insert into #tab2(id) values(1)
insert into #tab2(id) values(3)
insert into #tab2(id) values(4)select id from #tab1
except
select id from #tab2