Redshift多条件Join仅返回某一条件不匹配记录的查询问题

• 关联条件错误：JOIN时误用两张表的自增主键id作为关联键，没有匹配业务维度的ID字段，关联逻辑完全不符合需求
• 匹配逻辑错误：直接使用mytable1.Numbers <> mytable2.Numbers做不等值匹配，会产生笛卡尔积，无法正确筛选出「同ID下Numbers不在第一张表集合」的记录
• 分组逻辑错误：按自增主键id分组而非业务字段ID，和预期输出的分组维度不一致

以下为适配MySQL 8.0+的实现，运行后完全匹配预期输出：

SELECT 
  t2.ID,
  GROUP_CONCAT(DISTINCT t2.Numbers ORDER BY t2.Numbers SEPARATOR ',') AS unauth_list,
  COUNT(DISTINCT t2.Numbers) AS unauth_count
FROM myTable2 t2
-- 筛选ID存在于第一张表的记录
INNER JOIN (
  SELECT DISTINCT ID FROM myTable
) t1_id ON t2.ID = t1_id.ID
-- 匹配同ID下Numbers相同的记录，后续过滤匹配成功的
LEFT JOIN myTable t1 
  ON t2.ID = t1.ID 
  AND t2.Numbers = t1.Numbers
WHERE t1.ID IS NULL
GROUP BY t2.ID
HAVING unauth_count > 0;

如果是Oracle环境，只需将聚合函数替换为Oracle原生语法即可：

SELECT 
  t2.ID,
  LISTAGG(DISTINCT t2.Numbers, ',') WITHIN GROUP (ORDER BY t2.Numbers) AS unauth_list,
  COUNT(DISTINCT t2.Numbers) AS unauth_count
FROM myTable2 t2
INNER JOIN (SELECT DISTINCT ID FROM myTable) t1_id ON t2.ID = t1_id.ID
LEFT JOIN myTable t1 ON t2.ID = t1.ID AND t2.Numbers = t1.Numbers
WHERE t1.ID IS NULL
GROUP BY t2.ID
HAVING COUNT(DISTINCT t2.Numbers) > 0;

• 给两张表的(ID, Numbers)字段创建联合索引，查询时可直接走索引覆盖，避免全表扫描
• 数据量过大时可按ID范围分批次查询，避免单次查询占用内存过高导致OOM
• 若仅需统计数量不需要拼接Numbers列表，可删除GROUP_CONCAT/LISTAGG逻辑，进一步提升查询效率

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