--环境create table test_1 (a int ,b int)create table test_2 (c int, d int)insert test_1 select 1,2 union all select 1,null union all select 3,4insert test_2 select 1,2 union all select 1,null-- except select * from test_1 except select * from test_2/*a b----------- -----------3 4*/-- not existsselect * from test_1where not exists(select * from test_2 where a=c and b=d)/*a b----------- -----------1 NULL --这条记录对于test_1来说是唯一的3 4*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/feixianxxx/archive/2010/03/21/5402391.aspx
可以用2楼的 not exists 或者1楼的 except
不要用EXCEPT,因为会去掉重复的数据。。
EXCEPT ALL WITH CTE AS ( select *,ROW_NUMBER() over(partition by .. order by ..) AS RN from test_1 except select *,ROW_NUMBER() over(partition by .. order by ..) AS RN from test_2 )SELECT * FROM CTE
select * from tb2 t where not exists(select 1 from tb1 where id=t.id)或者select * from tb2 t where id not in(select id from tb1)
或者1楼的 except
WITH CTE AS (
select *,ROW_NUMBER() over(partition by .. order by ..) AS RN from test_1 except select *,ROW_NUMBER() over(partition by .. order by ..) AS RN from test_2
)SELECT * FROM CTE