MS Access 365左连接固定值列时异常问题问询

问题背景 #

你在MS Access 365（版本1908）里做左连接时遇到了奇怪的问题：当子查询返回的是固定值列，并把这个固定值作为连接条件的一部分时，左连接没有返回左表（TableA）的所有记录，这明显不符合左连接的预期行为。

具体来说，当你把子查询里的CATEG列用Iif计算生成时，查询能正常返回TableA的全部记录；但把CATEG改成固定值"CATEG_NEG"后，结果只保留了a.CATEG = "CATEG_NEG"的记录，相当于把左连接变成了内连接。

原因剖析 #

这其实是Access的Jet/ACE查询引擎的一个优化逻辑导致的意外行为。当引擎识别到子查询里的CATEG是一个固定值（而非动态计算的列），并且在ON子句中与左表的CATEG做等值比较时，它会错误地将这个连接条件优化成类似WHERE子句的过滤条件——引擎认为只有当a.CATEG等于固定值时才有可能匹配到右表记录，所以直接过滤掉了不满足该条件的左表行，违背了左连接“保留左表所有记录”的核心规则。

而当你用Iif(FIELD2>0, "CATEG_POS", "CATEG_NEG")生成CATEG时，引擎会把它视为动态计算列，不会触发这种过度优化，因此能正常保留左表的全部记录。

解决方案（符合你不能移除b.CATEG = a.CATEG的限制） #

针对这个问题，我们可以通过“欺骗”引擎，让它把固定值列当成动态计算列来处理，从而避免过度优化。这里有几个可行的方法：

方法1：用无差别分支的IIf生成列 #

即使逻辑上CATEG必然是"CATEG_NEG"，也用IIf包装一下，让引擎认为这是动态计算的结果：

SELECT * FROM TableA a LEFT JOIN (
 SELECT FIELD1, IIf(FIELD2 < 0, "CATEG_NEG", "CATEG_NEG") AS CATEG
 FROM TableB WHERE FIELD2 < 0
) b ON b.FIELD1 = a.FIELD1 AND b.CATEG = a.CATEG

这个IIf的两个分支返回的是同一个值，但引擎会将其视为计算列，不会触发过滤优化，这样就能返回TableA的所有记录了。

方法2：用字符串函数包装固定值 #

用一个简单的字符串函数（比如Mid、Trim）来包装固定值，让引擎认为这是动态计算的结果：

SELECT * FROM TableA a LEFT JOIN (
 SELECT FIELD1, Trim("CATEG_NEG") AS CATEG
 FROM TableB WHERE FIELD2 < 0
) b ON b.FIELD1 = a.FIELD1 AND b.CATEG = a.CATEG

Trim在这里没有实际作用，但足以让引擎放弃对固定值的优化逻辑。

方法3：嵌套子查询生成固定值 #

把固定值放在一个嵌套的子查询里，也能绕过引擎的优化：

SELECT * FROM TableA a LEFT JOIN (
 SELECT FIELD1, (SELECT "CATEG_NEG") AS CATEG
 FROM TableB WHERE FIELD2 < 0
) b ON b.FIELD1 = a.FIELD1 AND b.CATEG = a.CATEG

验证结果 #

使用上述方法后，你应该能得到符合左连接预期的结果：

ID a.FIELD1 a.CATEG b.FIELD1 b.CATEG
1 A CATEG_POS null null
2 B CATEG_NEG null CATEG_NEG
3 C CATEG_POS null null
4 D CATEG_NEG D CATEG_NEG
5 E CATEG_NEG E CATEG_NEG

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