Oracle SQL中ANSI与非ANSI连接语法结果差异的逻辑咨询

问题背景 #

我们有一条ANSI标准的三表右连接查询，基于Oracle默认HR schema返回106行数据：

select *
from employees e
right join departments d on e.department_id = d.department_id
right join jobs j on e.job_id = j.job_id;

但转成Oracle非ANSI的(+)外连接语法后，返回了600行数据，明显出现了departments与jobs表的交叉连接：

select *
from employees e, departments d, jobs j
where e.department_id(+) = d.department_id
and e.job_id(+) = j.job_id;

下面详细拆解非ANSI版本查询的执行逻辑：

非ANSI (+)外连接的核心规则 #

在Oracle的非ANSI外连接语法中，column(+) = value表示将column所在的表作为外连接的从表（被驱动表），value所在的表作为主表（驱动表）——主表的所有行都会被保留，从表中没有匹配的行则填充NULL。

当多个(+)条件都指向同一个从表时，Oracle会先将所有主表做交叉连接，再与从表做外连接。

非ANSI查询的执行步骤 #

步骤1：主表先做交叉连接 #

查询中，departments d和jobs j都是主表（因为它们的列没有带(+)），Oracle会先对这两个表执行笛卡尔积（交叉连接）：

• departments表有27行，jobs表有19行，交叉连接后得到 27 * 19 = 513 行中间结果。
• 这一步的每一行都是一个部门和一个职位的组合，不管这个组合在实际业务中是否存在对应的员工。

步骤2：与从表做左外连接 #

将步骤1得到的513行交叉连接结果作为主表，与employees e（从表）执行左外连接，连接条件是：

e.department_id = d.department_id AND e.job_id = j.job_id

• 对于每一个「部门+职位」的组合，如果employees表中存在同时属于该部门且担任该职位的员工，就将员工数据匹配到该行；
• 如果没有匹配的员工，employees表的所有列都会填充NULL。
• 最终的结果行数会略大于513（因为可能存在一个「部门+职位」组合对应多个员工的情况），这就是为什么你得到600行的原因。

与ANSI语法的差异对比 #

ANSI右连接的执行逻辑是按顺序逐步关联：

1. 先执行employees e right join departments d：以departments为主表，匹配employees，得到包含所有部门的106行结果（有员工的部门对应员工数据，无员工的部门员工列NULL）；
2. 再将这106行结果与jobs j做右连接：以jobs为主表，基于e.job_id = j.job_id匹配上一步的结果，不会出现departments与jobs直接交叉的情况，因此行数保持在106左右。

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