如何连接两个表生成含3类Code存在标识及Category变更标识的结果表

你可以通过全外连接两张表后计算标识位的方式实现需求，以下是标准SQL的实现代码：

SELECT
  COALESCE(a.Code, b.Code) AS Code,
  COALESCE(a.Description, b.Description) AS Description,
  CASE WHEN a.Code IS NOT NULL AND b.Code IS NULL THEN 'Y' ELSE 'N' END AS `Code in Table A only`,
  CASE WHEN b.Code IS NOT NULL AND a.Code IS NULL THEN 'Y' ELSE 'N' END AS `Code in Table B only`,
  CASE WHEN a.Code IS NOT NULL AND b.Code IS NOT NULL THEN 'Y' ELSE 'N' END AS `Code found in both tables`,
  CASE WHEN a.Code IS NOT NULL AND b.Code IS NOT NULL AND a.Category != b.Category THEN 'Y' ELSE 'N' END AS `Changed Category`
FROM table_a a
FULL OUTER JOIN table_b b
ON a.Code = b.Code
ORDER BY Code;

逻辑说明 #

• 用FULL OUTER JOIN基于Code字段关联两张表，确保两边所有的Code都不会被遗漏
• COALESCE函数用于取优先存在的值，Code和Description优先取表A的，表A不存在时自动取表B的对应值
• 四个标识位都通过简单的非空判断、等值判断即可实现，和需求规则完全匹配
• 最终输出的结果和你给出的预期格式完全一致

如果你使用的数据库不支持FULL OUTER JOIN（比如MySQL 8.0以下版本），可以用以下写法替代：

SELECT * FROM (
  -- 左连接获取所有表A的Code对应数据
  SELECT
    a.Code AS Code,
    a.Description AS Description,
    CASE WHEN b.Code IS NULL THEN 'Y' ELSE 'N' END AS `Code in Table A only`,
    'N' AS `Code in Table B only`,
    CASE WHEN b.Code IS NOT NULL THEN 'Y' ELSE 'N' END AS `Code found in both tables`,
    CASE WHEN b.Code IS NOT NULL AND a.Category != b.Category THEN 'Y' ELSE 'N' END AS `Changed Category`
  FROM table_a a
  LEFT JOIN table_b b ON a.Code = b.Code
  UNION ALL
  -- 右连接获取仅在表B中存在的Code对应数据
  SELECT
    b.Code AS Code,
    b.Description AS Description,
    'N' AS `Code in Table A only`,
    'Y' AS `Code in Table B only`,
    'N' AS `Code found in both tables`,
    'N' AS `Changed Category`
  FROM table_b b
  LEFT JOIN table_a a ON a.Code = b.Code
  WHERE a.Code IS NULL
) t
ORDER BY Code;

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