select * from A right join B on A.a=B.b where A.a is null
select A.a from A left join B on A.a=B.b where B.b is null
select A.a from A where not exists(select 1 from B where A.a=B.b)
declare @A table (id int,a int) declare @B table (id int,b int)insert @A select 1,2 union all select 3,4 union all select 5,6insert @B select 7,2 union all select 8,9 union all select 9,5 union all select 4,3select aa.* from @A aa where not exists (select 1 from @B bb where aa.a=bb.b) /* id a ----------- ----------- 3 4 5 6(2 行受影响) */
select * from A except select * from B
select a.* from a not in (select b from b)
select a.* from a where a not in (select b from b)
from A right join B
on A.a=B.b
where A.a is null
select A.a
from A left join B on A.a=B.b
where B.b is null
select A.a from A where not exists(select 1 from B where A.a=B.b)
declare @B table (id int,b int)insert @A
select 1,2 union all
select 3,4 union all
select 5,6insert @B
select 7,2 union all
select 8,9 union all
select 9,5 union all
select 4,3select aa.*
from @A aa
where not exists (select 1
from @B bb
where aa.a=bb.b)
/*
id a
----------- -----------
3 4
5 6(2 行受影响)
*/
from A
except
select *
from B