Oracle WITH子句关联两表查询结果错误的原因及解法

• 定义的find_emp公共表表达式（即WITH子句声明的临时结果集，简称CTE）全程没有被后续查询引用，属于冗余无效代码，完全没有发挥WITH子句的作用
• 日期匹配逻辑错误：IN子查询仅判断员工的hire_date是否存在于全表所有员工的start_date中，没有绑定员工ID做同员工维度的匹配，会出现跨员工的日期误匹配
• 遗漏核心校验规则：既没有保证日期匹配是同一员工的入职日期和岗位开始日期，也没有校验当前部门ID和初始任职部门ID相等的条件，完全不符合需求的匹配规则
• 子查询中重复使用别名e关联employees表，和外层查询的表别名冲突，很容易引发逻辑歧义

WITH子句（CTE）本质是把查询中逻辑独立的结果集提前定义，完全支持多表关联场景，不存在仅能用于单表的限制。你可以把原来写在JOIN后的岗位历史子查询提前定义为CTE，再做关联匹配即可，对应SQL如下：

WITH emp_initial_job AS (
    SELECT
        employee_id,
        department_id AS initial_dep,
        start_date
    FROM hr.job_history
)
SELECT DISTINCT
    e.employee_id,
    e.last_name,
    e.hire_date,
    e.department_id AS current_dep,
    j.initial_dep AS prev_dep
FROM hr.employees e
JOIN emp_initial_job j
    ON e.employee_id = j.employee_id
WHERE
    e.hire_date = j.start_date
    AND e.department_id = j.initial_dep;

这个写法和你已经验证正确的子查询JOIN逻辑完全一致：

• 先通过CTE提取所有员工的岗位历史记录，拿到每个历史任职条目的员工ID、部门、开始日期
• 按员工ID关联员工当前信息表和历史任职CTE，保证匹配的是同一个员工的记录
• 严格按照需求添加两个过滤条件：入职日期和初始岗位开始日期一致、当前部门和初始部门一致，最终返回的结果和正确子查询写法完全等价。

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