版本化实体Employee与Address表外连接查询问题

问题分析 #

当前查询无法匹配员工503对应的地址记录，核心原因是：员工表中empid=503的VER_ID=23，但地址表中ADId=103的VER_ID=-1，原查询要求ADId和VER_ID同时匹配，导致这条记录无法关联；而添加OR a.VER_ID=-1的写法在Oracle老式外连接语法中逻辑不成立，无法正确触发匹配。

解决方案 #

推荐使用ANSI标准LEFT JOIN语法，逻辑更清晰且易维护：

SELECT ai.*, a.*
FROM employee ai
LEFT JOIN ADDRESS a 
  ON ai.ADId = a.ADId
  AND (ai.VER_ID = a.VER_ID OR a.VER_ID = -1);

语法说明 #

• LEFT JOIN确保所有员工记录都会被返回，即使没有匹配的地址记录（如果存在的话）。
• ON子句中的条件：地址的ADId必须与员工的ADId匹配，同时满足地址VER_ID与员工VER_ID一致，或者地址VER_ID=-1，完美覆盖需求场景。

如果必须使用Oracle老式外连接语法，可使用以下写法（逻辑等价但可读性较差）：

SELECT ai.*, a.*
FROM employee ai, ADDRESS a
WHERE ai.ADId = a.ADId (+)
AND (ai.VER_ID = a.VER_ID (+) OR a.VER_ID = -1)
OR (a.ADId IS NULL AND NOT EXISTS (
    SELECT 1 FROM ADDRESS a2 
    WHERE a2.ADId = ai.ADId 
    AND (ai.VER_ID = a2.VER_ID OR a2.VER_ID = -1)
));

验证结果 #

执行上述查询后，将返回预期结果：

500 12  100     100 12 abc
501 13  101     101 13 bc
502 22  102     102 22 def
503 23  103     103 -1 ghi

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