Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Anuj.

Asked: August 08, 2016 - 11:54 am UTC

Last updated: January 18, 2017 - 1:53 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

I wrote below two queries, one written using ANSI syntax and other using conventional syntax. While writing these, I expected them to be logically equivalent and return same number of records (whatever that may be). When I actually ran these queries, I got following results:

ANSI: 22 records.
Conventional: 14 records.

For ANSI, it seems to be going in "order", first joining T2 and T3 and then right joining the result with T1. All good so far.
For Conventional, oracle is plainly ignoring the (+) and doing inner joins.

So, I want to understand the following:

1. What is making these two queries logically different?
2. How can this different in behaviour be explained?

with tbl_1 as (select level col1,
                      'T1_C2_R' || to_char(level) col2
                 from dual
              connect by level <= 10),
     tbl_2 as (select mod(level,5) col1,
                      'T2_C2_R' || to_char(level) col2
                 from dual
              connect by level <= 10),
     tbl_3 as (select mod(level,3) col1,
                      'T3_C2_R' || to_char(level) col2
                 from dual
              connect by level <= 10)
select *
  from tbl_2 t2 
       inner join
       tbl_3 t3 on (t2.col1 = t3.col1)
       right join
       tbl_1 t1 on (t1.col1 = t2.col1)
 order by t1.col1, t2.col1, t3.col1;

---
with tbl_1 as (select level col1,
                      'T1_C2_R' || to_char(level) col2
                 from dual
              connect by level <= 10),
     tbl_2 as (select mod(level,5) col1,
                      'T2_C2_R' || to_char(level) col2
                 from dual
              connect by level <= 10),
     tbl_3 as (select mod(level,3) col1,
                      'T3_C2_R' || to_char(level) col2
                 from dual connect by level <= 10)
select *
  from tbl_2 t2,
       tbl_3 t3,
       tbl_1 t1
 where t2.col1 = t3.col1
   and t1.col1 = t2.col1(+)
 order by t1.col1, t2.col1, t3.col1;

and Chris said...

The two queries aren't equivalent!

In the ANSI version, you're:

- Getting the results from T1
- Outer joining this to the join of T2 & T3

In the Oracle version, you're:

- Inner joining T2 to T3
- Inner joining this to T1

The fact you have the (+) against T2 is irrelevant. Once you've inner joined a table with Oracle syntax, it's always inner joined. So the:

where t2.col1 = t3.col1


"cancels out" the outer join at in the next line!

To make the Oracle version equivalent to the ANSI version, you need to do something like:

with tbl_1 as (select /*+ qb_name (t1) */level col1,
                      'T1_C2_R' || to_char(level) col2
                 from dual
              connect by level <= 10),
     tbl_2 as (select /*+ qb_name (t2) */mod(level,5) col1,
                      'T2_C2_R' || to_char(level) col2
                 from dual
              connect by level <= 10),
     tbl_3 as (select /*+ qb_name (t3) */mod(level,3) col1,
                      'T3_C2_R' || to_char(level) col2
                 from dual connect by level <= 10)
select *
  from (select t2.col1 t2_col1, t2.col2 t2_col2, t3.col1 t3_col1, t3.col2 t3_col2
 from tbl_2 t2, tbl_3 t3
 where  t2.col1 = t3.col1) tt,
       tbl_1 t1
 where t1.col1 = tt.t2_col1 (+)
 order by t1.col1, t2_col1, t3_col1;

T2_COL1  T2_COL2   T3_COL1  T3_COL2    COL1  COL2       
1        T2_C2_R6  1        T3_C2_R10  1     T1_C2_R1   
1        T2_C2_R6  1        T3_C2_R4   1     T1_C2_R1   
1        T2_C2_R1  1        T3_C2_R10  1     T1_C2_R1   
1        T2_C2_R1  1        T3_C2_R7   1     T1_C2_R1   
1        T2_C2_R6  1        T3_C2_R7   1     T1_C2_R1   
1        T2_C2_R1  1        T3_C2_R4   1     T1_C2_R1   
1        T2_C2_R6  1        T3_C2_R1   1     T1_C2_R1   
1        T2_C2_R1  1        T3_C2_R1   1     T1_C2_R1   
2        T2_C2_R2  2        T3_C2_R8   2     T1_C2_R2   
2        T2_C2_R2  2        T3_C2_R5   2     T1_C2_R2   
2        T2_C2_R7  2        T3_C2_R5   2     T1_C2_R2   
2        T2_C2_R2  2        T3_C2_R2   2     T1_C2_R2   
2        T2_C2_R7  2        T3_C2_R2   2     T1_C2_R2   
2        T2_C2_R7  2        T3_C2_R8   2     T1_C2_R2   
                                       3     T1_C2_R3   
                                       4     T1_C2_R4   
                                       5     T1_C2_R5   
                                       6     T1_C2_R6   
                                       7     T1_C2_R7   
                                       8     T1_C2_R8   
                                       9     T1_C2_R9   
                                       10    T1_C2_R10

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Anuj Parashar, August 08, 2016 - 11:50 pm UTC

Thanks Chris,

"The fact you have the (+) against T2 is irrelevant. Once you've inner joined a table with Oracle syntax, it's always inner joined."

With this information at hand, the results seem to be consistent. If I change the other join
t2.col1 = t3.col1

to
t2.col1 = t3.col1(+)

"outer" join is performed on both the tables. I would prefer an error in the initial example though instead of the "outer" join getting ignored.

Syntax Vs Semantics

Coder, August 10, 2016 - 6:05 am UTC

is the case of correct syntax for not the semantics?
Connor McDonald
August 10, 2016 - 9:46 am UTC

Not sure what you mean? Could you clarify?

To Anuj | on Outer join elimination

Rajeshwaran, Jeyabal, August 10, 2016 - 11:34 am UTC

I would prefer an error in the initial example though instead of the "outer" join getting ignored.

Added QB_NAME hint to the SQL for better visibility of tables in explain plans.

Given this "Where" clause
where t2.col1 = t3.col1
  and t1.col1 = t2.col1(+)


It says we need to an equi join plus an outer join to derive the result sets.

But the optimizer went into other direction, did a Cartesian between TB3 and TB1 and finally did a join between that result set and TB2.

The below explain plan shows that in detail.

demo@ORA11G> explain plan for
  2  with tbl_1 as (select /*+ qb_name(tb1) */ level col1,
  3                        'T1_C2_R' || to_char(level) col2
  4                   from dual
  5                connect by level <= 10),
  6       tbl_2 as (select /*+ qb_name(tb2) */  mod(level,5) col1,
  7                        'T2_C2_R' || to_char(level) col2
  8                   from dual
  9                connect by level <= 10),
 10       tbl_3 as (select /*+ qb_name(tb3) */ mod(level,3) col1,
 11                        'T3_C2_R' || to_char(level) col2
 12                   from dual connect by level <= 10)
 13  select *
 14    from tbl_2 t2,
 15         tbl_3 t3,
 16         tbl_1 t1
 17   where t2.col1 = t3.col1
 18     and t1.col1 = t2.col1(+)
 19   order by t1.col1, t2.col1, t3.col1;

Explained.

demo@ORA11G> select * from table(dbms_xplan.display(format=>'+alias +outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2111040168

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |     1 |   114 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY                    |      |     1 |   114 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN                       |      |     1 |   114 |     6   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN           |      |     1 |    76 |     4   (0)| 00:00:01 |
|   4 |     VIEW                          |      |     1 |    38 |     2   (0)| 00:00:01 |
|*  5 |      CONNECT BY WITHOUT FILTERING |      |       |       |            |          |
|   6 |       FAST DUAL                   |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |     BUFFER SORT                   |      |     1 |    38 |     4   (0)| 00:00:01 |
|   8 |      VIEW                         |      |     1 |    38 |     2   (0)| 00:00:01 |
|*  9 |       CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|  10 |        FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|  11 |    VIEW                           |      |     1 |    38 |     2   (0)| 00:00:01 |
|* 12 |     CONNECT BY WITHOUT FILTERING  |      |       |       |            |          |
|  13 |      FAST DUAL                    |      |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$311C88A3
   4 - TB3          / T3@SEL$1
   5 - TB3
   6 - TB3          / DUAL@TB3
   8 - TB1          / T1@SEL$1
   9 - TB1
  10 - TB1          / DUAL@TB1
  11 - TB2          / T2@SEL$1
  12 - TB2
  13 - TB2          / DUAL@TB2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      CONNECT_BY_COMBINE_SW(@"TB1")
      NO_CONNECT_BY_FILTERING(@"TB1")
      CONNECT_BY_COMBINE_SW(@"TB3")
      NO_CONNECT_BY_FILTERING(@"TB3")
      CONNECT_BY_COMBINE_SW(@"TB2")
      NO_CONNECT_BY_FILTERING(@"TB2")
      USE_HASH(@"SEL$311C88A3" "T2"@"SEL$1")
      USE_MERGE_CARTESIAN(@"SEL$311C88A3" "T1"@"SEL$1")
      LEADING(@"SEL$311C88A3" "T3"@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      NO_ACCESS(@"SEL$311C88A3" "T2"@"SEL$1")
      NO_ACCESS(@"SEL$311C88A3" "T1"@"SEL$1")
      NO_ACCESS(@"SEL$311C88A3" "T3"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"TB1")
      OUTLINE(@"TB3")
      OUTLINE(@"TB2")
      OUTER_JOIN_TO_INNER(@"SEL$1" "T2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$311C88A3")
      OUTLINE_LEAF(@"TB1")
      OUTLINE_LEAF(@"TB3")
      OUTLINE_LEAF(@"TB2")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T2"."COL1"="T3"."COL1" AND "T1"."COL1"="T2"."COL1")
   5 - filter(LEVEL<=10)
   9 - filter(LEVEL<=10)
  12 - filter(LEVEL<=10)

75 rows selected.

demo@ORA11G>


The 10053 trace confirms the same.

OJE: Begin: find best directive for query block SEL$4 (#0)
OJE: Considering outer-join elimination on query block SEL$4 (#0)
OJE: considering predicate"T2"."COL1"="T3"."COL1"

OJE:      Converting outer join of TBL_2 and TBL_1 to inner-join.
considered
OJE: considering predicate"T1"."COL1"="T2"."COL1"

Outer join elimination

Rajeshwaran, Jeyabal, August 10, 2016 - 11:56 am UTC

Chris,

Could you help us to understand what do we gain by transforming the outerjoin to an inner-join ?

I dont find any changes to the COST, before and after the outer join transformed into an inner-join.

Yes the join order got changed by setting this parameter _optimizer_outer_join_to_inner to FALSE.

Since no changes to the COST, but what influences the optimizer to pick the Inner Join plan rather than the Outer-join plan

demo@ORA11G> create table tbl_1 as
  2  select level col1,
  3       'T1_C2_R' || to_char(level) col2
  4   from dual
  5  connect by level <= 10;

Table created.

demo@ORA11G>
demo@ORA11G> create table tbl_2 as
  2  select  mod(level,5) col1,
  3     'T2_C2_R' || to_char(level) col2
  4  from dual
  5  connect by level <= 10;

Table created.

demo@ORA11G>
demo@ORA11G> create table tbl_3 as
  2  select mod(level,3) col1,
  3     'T3_C2_R' || to_char(level) col2
  4  from dual connect by level <= 10;

Table created.

demo@ORA11G> begin
  2     dbms_stats.set_table_stats(user,'tbl_1',numrows=>1000000,numblks=>100000);
  3     dbms_stats.set_table_stats(user,'tbl_2',numrows=>1000000,numblks=>100000);
  4     dbms_stats.set_table_stats(user,'tbl_3',numrows=>1000000,numblks=>100000);
  5  end;
  6  /

PL/SQL procedure successfully completed.

demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select *
  2    from tbl_2 t2,
  3         tbl_3 t3,
  4         tbl_1 t1
  5   where t2.col1 = t3.col1
  6     and t1.col1 = t2.col1(+)
  7   order by t1.col1, t2.col1, t3.col1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2072707941

--------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  1000K|   108M|       |   213K  (1)| 00:00:41 |
|   1 |  SORT ORDER BY       |       |  1000K|   108M|   120M|   213K  (1)| 00:00:41 |
|*  2 |   HASH JOIN          |       |  1000K|   108M|    47M|   178K  (1)| 00:00:35 |
|   3 |    TABLE ACCESS FULL | TBL_1 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
|*  4 |    HASH JOIN         |       |  1000K|    72M|    47M|   112K  (1)| 00:00:22 |
|   5 |     TABLE ACCESS FULL| TBL_2 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
|   6 |     TABLE ACCESS FULL| TBL_3 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."COL1"="T2"."COL1")
   4 - access("T2"."COL1"="T3"."COL1")

demo@ORA11G> alter session set "_optimizer_outer_join_to_inner" =false;

Session altered.

demo@ORA11G> select *
  2    from tbl_2 t2,
  3         tbl_3 t3,
  4         tbl_1 t1
  5   where t2.col1 = t3.col1
  6     and t1.col1 = t2.col1(+)
  7   order by t1.col1, t2.col1, t3.col1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3283048800

--------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  1000K|   108M|       |   213K  (1)| 00:00:41 |
|   1 |  SORT ORDER BY       |       |  1000K|   108M|   120M|   213K  (1)| 00:00:41 |
|*  2 |   HASH JOIN          |       |  1000K|   108M|    47M|   178K  (1)| 00:00:35 |
|   3 |    TABLE ACCESS FULL | TBL_3 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
|*  4 |    HASH JOIN OUTER   |       |  1000K|    72M|    47M|   112K  (1)| 00:00:22 |
|   5 |     TABLE ACCESS FULL| TBL_1 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
|   6 |     TABLE ACCESS FULL| TBL_2 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T2"."COL1"="T3"."COL1")
   4 - access("T1"."COL1"="T2"."COL1"(+))

demo@ORA11G> set autotrace off
demo@ORA11G>

Chris Saxon
August 15, 2016 - 9:53 am UTC

Since no changes to the COST, but what influences the optimizer to pick the Inner Join plan rather than the Outer-join plan

I'm not following. It doesn't matter what you've set _optimizer_outer_join_to_inner to. The result of the query:

select * from tbl_2 t2, tbl_3 t3, tbl_1 t1
where t2.col1 = t3.col1
and t1.col1 = t2.col1(+)
order by t1.col1, t2.col1, t3.col1;


is still an inner join between the three tables. Setting _optimizer_outer_join_to_inner allows the optimizer to recognize this and (possibly) choose different join orders.

Outer join elimination

Rajeshwaran, Jeyabal, August 16, 2016 - 5:47 am UTC

Given this below query and evaluating the predicate as such - it conveys an equi-join between T2 and T3 and an Outer join between T1 and T2.
select *
  from tbl_2 t2,
       tbl_3 t3,
       tbl_1 t1
 where t2.col1 = t3.col1
   and t1.col1 = t2.col1(+)
 order by t1.col1, t2.col1, t3.col1;

but Explain plan shows this.
demo@ORA11G> select *
  2    from tbl_2 t2,
  3         tbl_3 t3,
  4         tbl_1 t1
  5   where t2.col1 = t3.col1
  6     and t1.col1 = t2.col1(+)
  7   order by t1.col1, t2.col1, t3.col1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2072707941

--------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  1000K|   108M|       |   213K  (1)| 00:00:41 |
|   1 |  SORT ORDER BY       |       |  1000K|   108M|   120M|   213K  (1)| 00:00:41 |
|*  2 |   HASH JOIN          |       |  1000K|   108M|    47M|   178K  (1)| 00:00:35 |
|   3 |    TABLE ACCESS FULL | TBL_1 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
|*  4 |    HASH JOIN         |       |  1000K|    72M|    47M|   112K  (1)| 00:00:22 |
|   5 |     TABLE ACCESS FULL| TBL_2 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
|   6 |     TABLE ACCESS FULL| TBL_3 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."COL1"="T2"."COL1")
   4 - access("T2"."COL1"="T3"."COL1")

No more "Outer joins" spotted in the Explain plan - the 10053 confirm the same.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T2"."COL1" "COL1",
  "T2"."COL2" "COL2",
  "T3"."COL1" "COL1",
  "T3"."COL2" "COL2",
  "T1"."COL1" "COL1",
  "T1"."COL2" "COL2"
FROM "DEMO"."TBL_2" "T2",
  "DEMO"."TBL_3" "T3",
  "DEMO"."TBL_1" "T1"
WHERE "T2"."COL1"="T3"."COL1"
AND "T1"."COL1"  ="T2"."COL1"
ORDER BY "T1"."COL1",
  "T2"."COL1",
  "T3"."COL1"


So the parameter that transforms the "Outer join" into an "Equi Join" is this "_optimizer_outer_join_to_inner" (by default set to TRUE)

When this parameter (_optimizer_outer_join_to_inner) is set to FALSE - the optimizer doesn't perform the "Outer join" to "Equi join" transformation.

demo@ORA11G> alter session set "_optimizer_outer_join_to_inner" =false;

Session altered.

demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select *
  2    from tbl_2 t2,
  3         tbl_3 t3,
  4         tbl_1 t1
  5   where t2.col1 = t3.col1
  6     and t1.col1 = t2.col1(+)
  7   order by t1.col1, t2.col1, t3.col1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3283048800

--------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  1000K|   108M|       |   213K  (1)| 00:00:41 |
|   1 |  SORT ORDER BY       |       |  1000K|   108M|   120M|   213K  (1)| 00:00:41 |
|*  2 |   HASH JOIN          |       |  1000K|   108M|    47M|   178K  (1)| 00:00:35 |
|   3 |    TABLE ACCESS FULL | TBL_3 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
|*  4 |    HASH JOIN OUTER   |       |  1000K|    72M|    47M|   112K  (1)| 00:00:22 |
|   5 |     TABLE ACCESS FULL| TBL_1 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
|   6 |     TABLE ACCESS FULL| TBL_2 |  1000K|    36M|       | 50354   (1)| 00:00:10 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T2"."COL1"="T3"."COL1")
   4 - access("T1"."COL1"="T2"."COL1"(+))


the 10053 Trace confirms the same.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T2"."COL1" "COL1",
  "T2"."COL2" "COL2",
  "T3"."COL1" "COL1",
  "T3"."COL2" "COL2",
  "T1"."COL1" "COL1",
  "T1"."COL2" "COL2"
FROM "DEMO"."TBL_2" "T2",
  "DEMO"."TBL_3" "T3",
  "DEMO"."TBL_1" "T1"
WHERE "T2"."COL1"="T3"."COL1"
AND "T1"."COL1"  ="T2"."COL1"(+)
ORDER BY "T1"."COL1",
  "T2"."COL1",
  "T3"."COL1"


Could you help us to understand
a) Why the optimizer transforms the "outer join" into an "Inner join" - by default ? what was the reason behind this transformation ?

Connor McDonald
August 16, 2016 - 2:21 pm UTC

The query:

select *
  from tbl_2 t2,
       tbl_3 t3,
       tbl_1 t1
 where t2.col1 = t3.col1
   and t1.col1 = t2.col1(+)
 order by t1.col1, t2.col1, t3.col1;


Inner joins T2 to T3.
Then it joins T1 via an inner join.

So the net result is all tables are inner joined. The fact you have a (+) operator is irrelevant.

By ignoring the "invalid" outer join, the optimizer may be able to come up with better plans.

Outer join elimination

Rajeshwaran, Jeyabal, August 17, 2016 - 6:58 am UTC

(+) operator is irrelevant

How does the optimizer came to the conclusion that "(+) operator is irrelevant" ?

I know that in case of constraints and relationships the optimizer has the ability to add "few predicates (aka constraint generated predicates)" and have "Table elimination" (in which redundant tables will be identified and eliminated at parsing) property.

but in this scenario since we don't have any constraints / relationships in place, So what helps the optimizer to understand "(+) operator is irrelevant" ? How does it come to that conclusion ?
Connor McDonald
August 17, 2016 - 8:25 am UTC

It's nothing to do with constraints. It's just maths. Take the where clause:

where t2.col1 = t3.col1 and t1.col1 = t2.col1(+)


Rearrange slightly and you have:

t1.col1 = t2.col1 (+) = t3.col1


This outer joins "in the middle". That doesn't make any sense!

Oracle took the decision that if a table is inner joined in a query, it's always inner joined. Even if someone stuck an outer join (+) somewhere else.

Inner joins are commutative. This means you can order the tables involved however you want. e.g.

a join b join c = b join c join a = c join a join b


Outer joins are not commutative. You can't rearrange them in this way:

a left join b left join c <> b left join c left join a <> c left join a left join b


So by ignoring the "false" outer join, more powerful optimizations are possible.

Outer join elimination

Rajeshwaran, Jeyabal, August 17, 2016 - 9:19 am UTC

Thanks got it.

ANSI vs + JOINs

Praveen Ray, January 11, 2017 - 8:53 am UTC

@ August 15, 2016 - 9:53 am UTC

WHY THE FOLLOWING IS NOT CANCELLING OUT THE OUTER JOIN

create table a (id int, val char(1));
create table b (id int, val char(1));
create table c (id int, val char(1));

insert into a values(1, 'A');
insert into a values(2, 'B');
insert into b values(1, 'X');
insert into b values(1, null);
insert into c values(1, 'M');
insert into c values(2, 'N');

select * from a, b, c
where a.id = b.id--(+) -- results differ
and b.val is null and a.id = c.id;

Connor McDonald
January 11, 2017 - 1:19 pm UTC

"b.val is null"

is a condition *without* the outer join, so by definition, the join to 'b' is an inner join.


SQL>
SQL> create table a (id int, val char(1));

Table created.

SQL> create table b (id int, val char(1));

Table created.

SQL> create table c (id int, val char(1));

Table created.

SQL>
SQL> insert into a values(1, 'A');

1 row created.

SQL> insert into a values(2, 'B');

1 row created.

SQL> insert into b values(1, 'X');

1 row created.

SQL> insert into b values(1, null);

1 row created.

SQL> insert into c values(1, 'M');

1 row created.

SQL> insert into c values(2, 'N');

1 row created.

SQL>
SQL> select * from a, b, c
  2  where a.id = b.id(+)
  3  and b.val is null and a.id = c.id;

        ID V         ID V         ID V
---------- - ---------- - ---------- -
         1 A          1            1 M
         2 B                       2 N

2 rows selected.

SQL>
SQL> select * from a, b, c
  2  where a.id = b.id
  3  and b.val is null and a.id = c.id;

        ID V         ID V         ID V
---------- - ---------- - ---------- -
         1 A          1            1 M

1 row selected.

SQL>
SQL> select * from a, b, c
  2  where a.id = b.id(+)
  3  and b.val(+) is null and a.id = c.id;

        ID V         ID V         ID V
---------- - ---------- - ---------- -
         1 A          1            1 M
         2 B                       2 N

2 rows selected.

SQL>
SQL>


Query misunderstood

Praveen Ray, January 12, 2017 - 6:52 am UTC

select * from a, b, c
where a.id = b.id(+) -- outer as well as inner
and b.val is null and a.id = c.id;

the result is not inner joined as per your previous statement?
Connor McDonald
January 18, 2017 - 1:53 am UTC

Sorry - I misread your initial post.

There are two "parts" to the predicates

- the join conditions
- the conditions to applied *after* the join

You see the same when using ANSI, ie, people code stuff like this:

T1 left outer join T2
on t1.x = t2.x           -- 1
and t1.w. = t2.z         -- 2
where t1.g = 'Hello'     -- 3


1 and 2 define how we will *join*, and the 3 reduces the resultset *after* the join.

So simplifying the original demo down to just 2 tables

select * from a, b
where a.id = b.id(+)
and b.val is null ;


says "Do an outer join from a to b"

SQL> select * from a, b
  2  where a.id = b.id(+);

  ID V   ID V
---- - ---- -
   1 A    1 X
   1 A    1
   2 B


and now with that result, look for rows where b.val is null

SQL> select * from a, b
  2  where a.id = b.id(+)
  3  and b.val is null ;

  ID V   ID V
---- - ---- -
   1 A    1
   2 B

2 rows selected.



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions