SQL中关联键在单表重复时如何合并两表、保留全量关联信息
现有方案无效的核心原因
- 用
IN子查询本质是单表行筛选,仅能从workers表中挑出id存在于firms表的员工行,没有跨表关联的逻辑,自然无法返回firms表的企业名称、注册资本等字段。 MERGE是用于跨表数据同步(将源表数据按匹配规则更新、插入到目标表)的写操作语句,不是用于关联查询的语法,加上你当前使用的SQL解析器(从报错特征判断为低版本SQLite)本身不支持该关键字,使用场景和“拼接两表字段查询结果”的目标完全不匹配。
正确实现方案
通过表关联(JOIN)即可实现两表字段的拼接,可根据数据保留范围选择对应的关联类型:
- 若需完整保留所有员工记录(哪怕该员工没有匹配到对应的所属企业),使用左连接
LEFT JOIN,未匹配到的企业字段会显示为NULL - 若需完整保留所有企业记录(哪怕该企业没有任何关联员工),使用右连接
RIGHT JOIN,未匹配到的员工字段会显示为NULL - 若需同时完整保留两张表的所有记录,在支持全外连接的数据库中使用全外连接
FULL OUTER JOIN即可
注意:两张表存在同名字段(
id、name),查询时建议加表别名区分,避免字段歧义。
针对你提供的样例数据,可直接运行的查询语句为:
SELECT w.id AS worker_id, w.name AS worker_name, w.sex, w.age, f.id AS firm_id, f.name AS firm_name, f.capital FROM workers w LEFT JOIN firms f ON w.id = f.id;
关联键重复场景的匹配效果
关联查询会逐行做值匹配,不会因为关联键重复丢失数据:
- workers表中两条
id=1的员工记录,会自动匹配firms表中id=1的「Bank Warburg」企业全量信息 - workers表中两条
id=2的员工记录,会自动匹配firms表中id=2的「Atlas Security Information」企业全量信息
执行后返回的结果为:
worker_id | worker_name | sex | age | firm_id | firm_name | capital ----------|-----------------|-------|-----|---------|----------------------------|-------- 1 | W. White | male | 54 | 1 | Bank Warburg | 13051 2 | Hank Schroder | male | 51 | 2 | Atlas Security Information | 42094 2 | Gus Fring | male | 50 | 2 | Atlas Security Information | 42094 1 | Skyler | male | 44 | 1 | Bank Warburg | 13051
若需要生成持久化的合并新表
在上述查询逻辑前增加CREATE TABLE 新表名 AS即可,无需使用MERGE做表合并操作:
CREATE TABLE worker_firm_merged AS SELECT w.id AS worker_id, w.name AS worker_name, w.sex, w.age, f.id AS firm_id, f.name AS firm_name, f.capital FROM workers w LEFT JOIN firms f ON w.id = f.id;
内容的提问来源于stack exchange,提问作者Lucas
相关产品推荐
相关产品推荐

