In 12C LEFT OUTER JOIN is enhanced so a single table can be the null-generated table for multiple tables

In previous releases of Oracle Database, in a query that performed outer joins of more than two pairs of tables, a single table could be the null-generated table for only one other table. Beginning with Oracle Database 12c, a single table can be the null-generated table for multiple tables.

Example

Prepare test data

CREATE TABLE test_tbl1
(id1 NUMBER);

CREATE TABLE test_tbl2
(id1 NUMBER);

CREATE TABLE test_tbl3
(id1 NUMBER);

INSERT INTO test_tbl1 VALUES(1);
INSERT INTO test_tbl1 VALUES(2);
INSERT INTO test_tbl1 VALUES(3);

INSERT INTO test_tbl2 VALUES(2);
INSERT INTO test_tbl2 VALUES(3);
INSERT INTO test_tbl2 VALUES(4);

INSERT INTO test_tbl3 VALUES(3);
INSERT INTO test_tbl3 VALUES(4);
INSERT INTO test_tbl3 VALUES(5);

COMMIT;

SELECT * FROM test_tbl1;

       ID1
----------
         1
         2
         3

SELECT * FROM test_tbl2;

       ID1
----------
         2
         3
         4

SELECT * FROM test_tbl3;

       ID1
----------
         3
         4
         5

and new supported syntax

SELECT a.id1 id1_a, b.id1 id1_b, c.id1 id1_c 
  FROM test_tbl1 a, test_tbl2 b, test_tbl3 c
 WHERE a.id1 = b.id1(+)
   AND c.id1 = b.id1(+)
ORDER BY 1; 

     ID1_A      ID1_B      ID1_C
---------- ---------- ----------
         1                     4
         1                     5
         1                     3
         2                     3
         2                     4
         2                     5
         3                     5
         3          3          3
         3                     4

It’s good to know that tables A, C are joined as CARTESIAN then outer join is used to table B

EXPLAIN PLAN FOR
SELECT a.id1 id1_a, b.id1 id1_b, c.id1 id1_c 
  FROM test_tbl1 a, test_tbl2 b, test_tbl3 c
 WHERE A.id1 = b.id1(+)
   AND c.id1 = b.id1(+)
ORDER BY 1;  

SELECT * 
  FROM TABLE(dbms_xplan.display(null, null, 'BASIC'));

Plan hash value: 1876081838
 
--------------------------------------------
| Id  | Operation              | Name      |
--------------------------------------------
|   0 | SELECT STATEMENT       |           |
|   1 |  SORT ORDER BY         |           |
|   2 |   HASH JOIN OUTER      |           |
|   3 |    MERGE JOIN CARTESIAN|           |
|   4 |     TABLE ACCESS FULL  | TEST_TBL1 |
|   5 |     BUFFER SORT        |           |
|   6 |      TABLE ACCESS FULL | TEST_TBL3 |
|   7 |    TABLE ACCESS FULL   | TEST_TBL2 |
--------------------------------------------

Have a fun 🙂

Tomasz