SQL查询多表均存在值的方法（Microsoft Access环境）

原查询报错原因 #

你当前使用的多层LEFT JOIN写法会触发笛卡尔积膨胀问题：如果同一个名称在任意一个目标表中存在多条匹配记录，多表连接后会生成各表匹配条数乘积的重复行。例如某名称在MassPC有2条匹配、Maine有3条匹配、NY有2条匹配，单这一个名称就会生成232*... 共几十条重复数据，随着表数量增加、单表匹配记录增多，结果集体量会指数级上涨，最终触发结果集过大的错误。
另外你用LEFT JOIN加IS NOT NULL过滤的写法本质是实现内连接效果，但完全没有规避重复行的问题，额外加的GROUP BY去重也会在超大数据量下增加运算负担。同时Key是Access的保留关键字，直接用做表名容易触发语法错误，建议加方括号转义。

优化后可直接运行的代码 #

改用EXISTS子查询逐表校验存在性，完全不会产生多表连接的重复行，查询效率远高于JOIN写法，Access数据库原生支持该语法：

SELECT [Key].UltimateParent
FROM [Key]
WHERE 
  EXISTS (SELECT 1 FROM MassPC WHERE MassPC.UltimateRollup = [Key].UltimateParent)
  AND EXISTS (SELECT 1 FROM Maine WHERE Maine.UltimateRollup = [Key].UltimateParent)
  AND EXISTS (SELECT 1 FROM NY WHERE NY.UltimateRollup = [Key].UltimateParent)
  AND EXISTS (SELECT 1 FROM Texas WHERE Texas.UltimateRollup = [Key].UltimateParent)
  AND EXISTS (SELECT 1 FROM Florida WHERE Florida.UltimateRollup = [Key].UltimateParent)

写法优势 #

• 无笛卡尔积冗余行问题，不需要额外加GROUP BY做去重运算，查询速度提升明显，不会出现结果集过大的报错
• 逻辑完全匹配需求：每个EXISTS子句独立校验名称在对应表中是否存在，只有所有子句都匹配的名称才会被返回
• 后续扩展简单：需要新增校验表时，只需要追加一行AND EXISTS判断即可，不需要调整Access多表JOIN要求的繁琐嵌套括号结构，不容易写错语法
• 自动忽略匹配字段的NULL值，不需要额外写IS NOT NULL的过滤条件

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