如何在pgAdmin中关联两个表，将城市、国家字段添加到第一个表

最简单的方式是通过两个表的共同关联字段emailaddress进行关联查询，可根据实际需求选择关联类型：

• 仅保留两个表中emailaddress能匹配上的表1记录，使用INNER JOIN：

SELECT 
    t1.candidateid,
    t1.date,
    t1."time",
    t1.campaign_id,
    t1.emailaddress,
    t1.email_size,
    t1.subject_length,
    t1.email_length,
    t2.city,
    t2.country
FROM dbo.learningtokenopenemail t1
INNER JOIN dbo.candidatedetails t2
    ON t1.emailaddress = t2.emailaddress

• 需要保留表1的全部记录，无匹配的city和country字段自动返回空值，使用LEFT JOIN，这也是大多数场景下的通用选择：

SELECT 
    t1.candidateid,
    t1.date,
    t1."time",
    t1.campaign_id,
    t1.emailaddress,
    t1.email_size,
    t1.subject_length,
    t1.email_length,
    t2.city,
    t2.country
FROM dbo.learningtokenopenemail t1
LEFT JOIN dbo.candidatedetails t2
    ON t1.emailaddress = t2.emailaddress

注意：如果emailaddress字段在dbo.candidatedetails表中存在重复值，关联后会出现数据行膨胀的情况，建议先确认dbo.candidatedetails的emailaddress为唯一值，或者提前对表2做去重处理。

内容的提问来源于stack exchange，提问作者Finlay MacFarlane