我看了一下代码,发现一个查询可以在mysql,mssql,oracle 11g上运行,但在oracle 12c中有不同的行为.

我已经推广了表格结构并进行了一些查询,并重现了这个问题.

create table thing (thing_id number,display_name varchar2(500));
create table thing_related (related_id number,thing_id number,thing_type varchar2(500));
create table type_a_status (related_id number,status varchar2(500));
create table type_b_status (related_id number,status varchar2(500));

insert into thing values (1,'first');
insert into thing values (2,'second');
insert into thing values (3,'third');
insert into thing values (4,'fourth');
insert into thing values (5,'fifth');
insert into thing_related values (101,1,'TypeA');
insert into thing_related values (102,2,'TypeB');
insert into thing_related values (103,3,'TypeB');
insert into thing_related (related_id,thing_id) values (104,4);

insert into type_a_status values (101,'OK');
insert into type_b_status values (102,'OK');
insert into type_b_status values (103,'NOT OK');

运行查询：

SELECT t.thing_id AS id,t.display_name as name,tas.status as type_a_status,tbs.status as type_b_status
FROM thing t LEFT JOIN thing_related tr 
  ON t.thing_id = tr.thing_id
LEFT JOIN type_a_status tas 
  ON (tr.related_id IS NOT NULL 
      AND tr.thing_type = 'TypeA' 
      AND tr.related_id = tas.related_id)
LEFT JOIN type_b_status tbs 
  ON (tr.related_id IS NOT NULL 
      AND tr.thing_type = 'TypeB' 
      AND tr.related_id = tbs.related_id)

在Oracle 11g上(这里是SQL Fiddle)：

ID | NAME   | TYPE_A_STATUS | TYPE_B_STATUS
 1 | first  |            OK | (null)
 2 | second |        (null) | OK
 3 | third  |        (null) | NOT OK
 4 | fourth |        (null) | (null)
 5 | fifth  |        (null) | (null)

但是,与Oracle 12c相同的架构,数据和查询：

ID | NAME   | TYPE_A_STATUS | TYPE_B_STATUS
 1 | first  |            OK | (null)
 2 | second |        (null) | OK
 3 | third  |        (null) | NOT OK
 4 | fourth |        (null) | (null)

似乎第二个外部连接没有带回任何东西,因为在“thing_related”中没有行加入.但是我不明白为什么外连接在这种情况下不返回null,就像在Oracle 11g,MysqL等中一样.

我一直在研究和发现文档,Oracle 12c对外连接有一些增强功能,但没有突出显示会影响到这一点的更改.

有没有人知道为什么这只是为了Oracle 12c,而且我如何最好地重写这个工作在12c和维护与11g,MysqL等的兼容性？

编辑：附加计划

Oracle 11g：

Oracle 12c：