如何统计两个输入表ID出现次数并关联Lookup表输出对应结果

问题需求 #

你有3张表：2张数据表Tab1、Tab2，1张维度表Tab3，需要统计Tab3中每个ID是否在Tab1或Tab2中出现过，出现过计数为1，未出现计数为0。

正确SQL写法 #

写法1（UNION合并去重，兼容性最高）

SELECT 
  COUNT(t.id) AS `Count`,
  Tab3.Name
FROM Tab3
LEFT JOIN (
  -- 合并两张表的ID并自动去重，每个ID只要出现过就仅保留1条
  SELECT Id FROM Tab1
  UNION
  SELECT ID FROM Tab2
) t ON t.Id = Tab3.ID
GROUP BY Tab3.ID, Tab3.Name
ORDER BY Tab3.ID;

写法2（EXISTS判断，性能更优，适合数据量较大的场景）

SELECT 
  CASE 
    WHEN EXISTS (SELECT 1 FROM Tab1 WHERE Tab1.Id = Tab3.ID) 
      OR EXISTS (SELECT 1 FROM Tab2 WHERE Tab2.ID = Tab3.ID)
    THEN 1 
    ELSE 0 
  END AS `Count`,
  Tab3.Name
FROM Tab3
ORDER BY Tab3.ID;

原写法错误说明 #

你之前的查询存在两个核心问题：

1. 关联逻辑错误：FROM子句先引用Tab3，第一次关联Tab1时的关联条件写的是a.id = b.id，此时Tab2还未被关联，语法不成立
2. 计数逻辑错误：COUNT(DISTINCT a.id) + COUNT(DISTINCT b.id)会将同时出现在两张表的ID计数为2，和你需要的「只要出现过就算1次」的逻辑不符

内容的提问来源于stack exchange，提问作者Fhd.ashraf