排除匹配的行 [英] Excluding matching Rows

问题描述

我有2个表，我想在一个公共列上加入这两个表，然后排除来自第二个表的匹配行（但返回表2中的所有其他内容以及表1中的所有内容）

I have 2 table and I would like to join these 2 tables on a common column and then exclude the matching rows from the second table ( but return everything else from Table 2 and everything from Table 1)

以下是我的截图想要。最终输出是2个表的结合  （不包括表2中的匹配行）匹配基于MatchCol列

Here is the screenshot of what I want . final output is union of 2 tables  ( which excludes the matching rows from Table 2) Match is based on MatchCol Column

提前致谢

推荐答案

use tempdb
go
create table #t1
(Mathcol int ,
col1 char(10),
[Table identifier] char(10))
insert #t1 
select 1,'ab','T1'
insert #t1 
select 2,'aa','T1'
insert #t1 
select 3,'vv','T1'
insert #t1 
select 4,'sd','T1'
insert #t1 
select 5,'fg','T1'
insert #t1 
select 6,'hh','T1'

create table #t2
(Mathcol int ,
col1 char(10),
[Table identifier] char(10))

insert #t2 
select 1,'PPP','T2'
insert #t2 
select 9,'HJHJ','T2'
insert #t2 
select 3,'FDD','T2'
insert #t2 
select 4,'BB','T2'
insert #t2 
select 90,'HJJ','T2'
insert #t2 
select 55,'RTYR','T2'

select * from #t1 union 
select * from #t2 t2 where t2.Mathcol not in (select Mathcol from #t1)



Mathcol     col1       Table identifier
----------- ---------- ----------------
1           ab         T1        
2           aa         T1        
3           vv         T1        
4           sd         T1        
5           fg         T1        
6           hh         T1        
9           HJHJ       T2        
55          RTYR       T2        
90          HJJ        T2        

(строк обработано: 9)

这篇关于排除匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！