table1
id dw
1 test1
2 test2
3 test3table 2
id dw
1 test1
2 test2
3 test2
4 test 4
查询结果
当 table1.id=table2.id
id dw
1 test1
2 test2
3 test3
当 table1.id<>table2.id
id dw
4 test 4
id dw
1 test1
2 test2
3 test3table 2
id dw
1 test1
2 test2
3 test2
4 test 4
查询结果
当 table1.id=table2.id
id dw
1 test1
2 test2
3 test3
当 table1.id<>table2.id
id dw
4 test 4
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab1') is null
drop table #tab1
Go
Create table #tab1([id] int,[dw] nvarchar(25))
Insert #tab1
select 1,N'test1' union all
select 2,N'test2' union all
select 3,N'test3'
Go
if not object_id(N'Tempdb..#tab2') is null
drop table #tab2
Go
Create table #tab2([id] int,[dw] nvarchar(25))
Insert #tab2
select 1,N'test1' union all
select 2,N'test2' union all
select 3,N'test2' union all
select 4,N'test4'
Go
--测试数据结束SELECT b.id,
CASE
WHEN a.id = b.id THEN a.dw
ELSE b.dw
END AS dw
FROM #tab1 AS a
RIGHT JOIN #tab2 AS b
ON a.id = b.id
select id, coalesce( dw2,dw1)
from
(
select table2.id, table2.dw as dw1, table1.dw as dw2\
from table2
left join table1 on table2.id = table1.id
) s
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab1') is null
drop table #tab1
Go
Create table #tab1([id] int,[dw] nvarchar(25))
Insert #tab1
select 1,N'test1' union all
select 2,N'test2' union all
select 3,N'test3'
Go
if not object_id(N'Tempdb..#tab2') is null
drop table #tab2
Go
Create table #tab2([id] int,[dw] nvarchar(25))
Insert #tab2
select 1,N'test1' union all
select 2,N'test2' union all
select 3,N'test3' union all
select 4,N'test4'
Go
--测试数据结束--求交集
SELECT * FROM #tab1 INTERSECT SELECT * FROM #tab2;--求差集
SELECT * FROM #tab1 EXCEPT SELECT * FROM #tab2
UNION
SELECT * FROM #tab2 EXCEPT SELECT * FROM #tab1;