Sql Query 帮助从两个表中获取不匹配的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5840217/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql Query help to get non matching records from two tables
提问by Prady
I am trying to get non matching records from 2 tables
我正在尝试从 2 个表中获取不匹配的记录
For ex
对于前任
TableA
ID Account
1 Acc1
2 Acc2
3 Acc3
TableB
Opp Accountid
Opp1 1
Opp2 2
Opp3 4
I need to know which accountid which is present in TableB but not available in TableA. It would be wonderful someone could explain how you would approach this query.
我需要知道在 TableB 中存在但在 TableA 中不可用的 accountid。如果有人能解释您将如何处理此查询，那就太好了。
Required record would be Opp3 of tableB
所需的记录将是 tableB 的 Opp3
Thanks
谢谢
Prady
普拉迪
回答by Nighil
create table #one (id int,acc nvarchar(25))
insert into #one (id , acc) values(1,'one')
insert into #one (id , acc) values(2,'two')
insert into #one (id , acc) values(3,'three')
create table #two (acct nvarchar(25),ids int)
insert into #two (acct,ids) values('one',1)
insert into #two (acct,ids) values('two',3)
insert into #two (acct,ids) values('four',4)
select ids from #two EXCEPT select id from #one
drop table #one
drop table #two
test this one
测试这个
回答by David Fells
SELECT B.Accountid
FROM TableB AS B
LEFT
JOIN TableA AS A
ON A.ID = B.Accountid
WHERE A.ID IS NULL;
LEFT JOIN means it takes all the rows from the first table - if there are no matches on the first join condition, the result table columns for table B will be null - that's why it works.
LEFT JOIN 意味着它从第一个表中获取所有行 - 如果第一个连接条件没有匹配，表 B 的结果表列将为空 - 这就是它起作用的原因。
回答by Kumaran NJ
SELECT B.Accountid
FROM TableB AS B
LEFT JOIN TableA AS A ON A.ID = B.Accountid
WHERE A.ID IS NULL
回答by Kuntady Nithesh
try this
尝试这个
(select * from t1
except
select * from t2)
union
(select * from t2
except
select * from t1)
thinking that you have the same number of columns in both tables
认为两个表中的列数相同
query mentioned above select ids from #two EXCEPT select id from #one will give u non matching rows from only #two . it will neglect that of #one
上面提到的查询 select ids from #two EXCEPT select id from #one 将只从 #two 给你不匹配的行。它会忽略#one
回答by sajid
This will generate same results.
这将产生相同的结果。
select * from TableB where Accountid not in (select ID from TableA)
