一、通用场景：查找两张表之间的差异 #

如果是结构完全相同的两张表（列名、数据类型都一致），我平时处理这类需求常用两种方法：

• 方法1：用MINUS和UNION ALL组合，精准定位仅在单表存在的记录

  -- 仅在A表存在的记录
  SELECT * FROM A
  MINUS
  SELECT * FROM B;
  
  -- 仅在B表存在的记录
  SELECT * FROM B
  MINUS
  SELECT * FROM A;
  
  -- 一次性获取所有差异记录（合并两边的独有的数据）
  (SELECT * FROM A MINUS SELECT * FROM B)
  UNION ALL
  (SELECT * FROM B MINUS SELECT * FROM A);
  

• 方法2：用FULL OUTER JOIN，通过NULL值判断差异
  假设表有主键（比如id），可以这样关联后筛选：

  SELECT 
    COALESCE(A.id, B.id) AS record_id,
    A.*,
    B.*
  FROM A
  FULL OUTER JOIN B ON A.id = B.id
  WHERE A.id IS NULL OR B.id IS NULL;
  

如果两张表结构不同，就得先明确要对比的核心字段，基于公共字段关联后再对比目标列：

SELECT 
  A.name,
  A.user_type AS A_user_type,
  B.role AS B_role,
  CASE WHEN A.user_type != B.role THEN '值不匹配' ELSE '值一致' END AS diff_result
FROM A
JOIN B ON A.name = B.name
WHERE A.user_type != B.role;

二、你的特定场景：带前缀匹配的额外记录查找 #

针对你说的情况——A和B仅name是公共字段，要找A中基于name的额外记录，且B的name可能带dummy_或Test_前缀需要去除，我会这样写SQL：

核心思路是先清洗B表的name字段（去掉指定前缀），再用NOT EXISTS判断A的name不在清洗后的B集合里：

SELECT A.*
FROM A
WHERE NOT EXISTS (
  SELECT 1
  FROM B
  -- 正则替换：匹配以dummy_或Test_开头的前缀，替换为空字符串
  WHERE REGEXP_REPLACE(B.name, '^(dummy_|Test_)', '') = A.name
);

如果数据量很大，担心正则表达式的性能，也可以用SUBSTR和INSTR的组合来实现，写法虽然繁琐但有时更稳定：

SELECT A.*
FROM A
WHERE NOT EXISTS (
  SELECT 1
  FROM B
  WHERE 
    -- 处理带dummy_前缀的情况
    (INSTR(B.name, 'dummy_') = 1 AND SUBSTR(B.name, 7) = A.name)
    OR
    -- 处理带Test_前缀的情况
    (INSTR(B.name, 'Test_') = 1 AND SUBSTR(B.name, 6) = A.name)
    OR
    -- B的name本身没有前缀的情况
    B.name = A.name
);

这样就能精准找出A表中那些在B表（去掉指定前缀后）没有匹配到的额外记录了。

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