postgre使用SQL查找数据库表中没有匹配键的匹配行

Question

我有一个包含大约10,000行遗留数据的旧表和一个包含大约500行数据的新表。两个表中的列是相同的。我需要将新表中的一些列与旧表中的列进行比较，并报告新表中重复的数据。

我已经研究了类似问题的文章，尝试表连接和哪里存在/哪里不存在子句，但我就是不能得到SQL的权利。我已经附上了我的最新版本

我认为给我带来麻烦的一个问题是没有"关键字"。如userid或类似的唯一标识符在两个表中。

我要做的是在"new"中找到数据表中除"reference_number"之外的所有行(不管是否存在)是重复的，即已经存在于"old"表。

我到目前为止有这个…

select 
old.reference_number
new.reference_number
new.component
new.privileges
new.protocol
new.authority
new.score
new.means
new.difficulty
new.hierarchy
new.interaction
new.scope
new.conf
new.integrity
new.availability
new.version
from old, new
where
old.component = new.component
old.privileges = new.privileges
old.protocol = new.protocol
old.authority = new.authority
old.score = new.score
old.means = new.means
old.difficulty = new.difficulty
old.hierarchy = new.hierarchy
old.interaction = new.interaction
old.scope = new.scope
old.conf = new.conf
old.integrity = new.integrity
old.availability = new.availability
old.version = new.version

我在这里试过了，但由于某种原因，它似乎没有取出所有的数据。

很明显，实际上在旧表中有更多的行在新表中重复，但我只得到从查询返回的少量行。

有人能看出为什么会这样吗，我应该用别的方法来处理这个问题吗?

如果有关系，这是Postgresql

谢谢你的帮助。

Answer 1

你可以这样做:

select distinct o.reference_number,
n.reference_number,
n.component,
n.privileges,
n.protocol,
n.authority,
n.score,
n.means,
n.difficulty,
n.hierarchy,
n.interaction,
n.scope,
n.conf,
n.integrity,
n.availability,
n.version
from new n
inner join old o
on o.component = n.component and
o.privileges = n.privileges and
o.protocol = n.protocol and
o.authority = n.authority and
o.score = n.score and
o.means = n.means and
o.difficulty = n.difficulty and
o.hierarchy = n.hierarchy and
o.interaction = n.interaction and
o.scope = n.scope and
o.conf = n.conf and
o.integrity = n.integrity and
o.availability = n.availability and
o.version = n.version

Answer 2

您应该使用左连接，然后只选择新值为null的行。SQL应该是这样的:

select 
old.reference_number
new.reference_number
new.component
new.privileges
new.protocol
new.authority
new.score
new.means
new.difficulty
new.hierarchy
new.interaction
new.scope
new.conf
new.integrity
new.availability
new.version
from old 
left join new
on
old.component = new.component
old.privileges = new.privileges
old.protocol = new.protocol
old.authority = new.authority
old.score = new.score
old.means = new.means
old.difficulty = new.difficulty
old.hierarchy = new.hierarchy
old.interaction = new.interaction
old.scope = new.scope
old.conf = new.conf
old.integrity = new.integrity
old.availability = new.availability
old.version = new.version
where new.component is null