SQL关联两张表去重ID、优先保留estimation为True记录的方案咨询

方案1：FULL OUTER JOIN 实现（符合关联查询需求） #

逻辑：通过全外连接按ID关联两张表，优先取COMPANIES_ESTIMATED的记录，该表不存在的ID再取COMPANIES_MIXED的记录，天然满足同ID优先保留estimation为True的规则。

SELECT
  COALESCE(ce.id, cm.id) AS id,
  COALESCE(ce.name, cm.name) AS name,
  COALESCE(ce.estimation, cm.estimation) AS estimation
FROM COMPANIES_ESTIMATED ce
FULL OUTER JOIN COMPANIES_MIXED cm
  ON ce.id = cm.id
ORDER BY id;

如果使用的是不支持FULL OUTER JOIN的数据库（如MySQL 8.0以前版本），可使用下方的方案2实现

方案2：UNION ALL + 窗口函数去重实现 #

逻辑：合并两张表的所有记录，给优先级更高的COMPANIES_ESTIMATED表记录打更高的排序权重，每个ID仅保留权重最高的一条记录即可。

WITH all_companies AS (
  SELECT id, name, estimation, 1 AS priority
  FROM COMPANIES_ESTIMATED
  UNION ALL
  SELECT id, name, estimation, 2 AS priority
  FROM COMPANIES_MIXED
),
ranked AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY priority ASC) AS rn
  FROM all_companies
)
SELECT id, name, estimation
FROM ranked
WHERE rn = 1
ORDER BY id;

提示：如果实际表名带空格，需要按所用数据库的语法转义表名，比如MySQL用反引号COMPANIES ESTIMATED，SQL Server用方括号[COMPANIES ESTIMATED]，Oracle用双引号"COMPANIES ESTIMATED"

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