X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Outerjoins in Oracle

Maria Colgan
Master Product Manager

Since Oracle Database version 6, Oracle has supported a restricted form of left outerjoin, which uses Oracle-specific syntax. In Oracle Database 9i, we introduced support for ANSI SQL 92/99 syntax for inner joins and various types of outerjoin. The Oracle syntax for left outerjoin and that of ANSI SQL 92/99 are not equivalent, as the latter is more expressive.

There appears to be some confusion about equivalence between ANSI outer join and Oracle outer join syntax. The following examples explain the equivalences and in-equivalences of these two syntaxes.

Oracle-Specific Syntax

Consider query A, which expresses a left outerjoin in the Oracle syntax. Here T1 is the left table whose non-joining rows will be retained and all non-joining rows of T2 will be null appended.

A.
        SELECT T1.d, T2.c
        FROM T1, T2
        WHERE T1.x = T2.x (+);

ANSI Left Outerjoin

In the ANSI outer join syntax, query A can be expressed as query B.

B.
        SELECT T1.d, T2.c
        FROM T1 LEFT OUTER JOIN T2
        ON (T1.x = T2.x);

Equivalence

Consider the following queries. In the Oracle semantics, the presence of (+) on the filter predicate (e.g., T2.y (+) > 5 in query C) indicates that this filter must be applied to the table T2 before the outer join takes place.

C.
        SELECT T1.d, T2.c
        FROM T1, T2
        WHERE T1.x = T2.x (+) and T2.y (+) > 5;

The ANSI left outer join query D is equivalent to C. Applying the filter on the right table in the left outer join is the same as combining the filter with the join condition.

D.
        SELECT T1.d, T2.c
        FROM T1 LEFT OUTER JOIN T2
        ON (T1.x = T2.x and T2.y > 5);

Similarly, the presence of (+) on the filter predicate, T2.y (+) IS NULL, in query E indicates that this filter must be applied to the table T2 before the outer join takes place.

E.
        SELECT T1.d, T2.c
        FROM T1, T2
        WHERE T1.x = T2.x (+) and T2.y (+) IS NULL;

The ANSI left outer join query F is equivalent to E.

F.
        SELECT T1.d, T2.c
        FROM T1 LEFT OUTER JOIN T2
        ON (T1.x = T2.x and T2.y IS NULL);

Consider query G. Oracle will apply the filter, T2.y IS NULL, in query G after the outer join has taken place. G will return only those rows of T2 that failed to join with T1 or those whose T2.y values happen to be null.

G.
        SELECT T1.d, T2.c
        FROM T1, T2
        WHERE T1.x = T2.x (+) and T2.y IS NULL;

The ANSI left outer join query H is equivalent to G, as the WHERE clause in H is applied after the left outer join is performed based on the condition specified in the ON clause.

H.
        SELECT T1.d, T2.c
        FROM T1 LEFT OUTER JOIN T2
        ON (T1.x = T2.x)
        WHERE T2.y IS NULL;

Consider query I, where the filter on the left table is applied before or after the outer join takes place.

I.
        SELECT T1.d, T2.c
        FROM T1, T2
        WHERE T1.x = T2.x (+) and T1.Z > 4;

The ANSI left outer join query J is equivalent to query I.

J.
        SELECT T1.d, T2.c
        FROM T1 LEFT OUTER JOIN T2
        ON (T1.x = T2.x)
        WHERE T1.Z > 4;

Lateral Views

In Oracle, ANSI left and right outerjoins are internally expressed in terms of left outerjoined lateral views. In many cases, a left outerjoined lateral view can be merged and the ANSI left (or right) outerjoin can be expressed entirely in terms of Oracle native left outerjoin operator. (A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause.)

Consider the ANSI left outer join query K, which is first represented internally as L.

K.
        SELECT T1.d, T2.c
        FROM T1 LEFT OUTER JOIN T2
        ON (T1.x = T2.x and T2.k = 5);

L.
        SELECT T1.d, LV.c
        FROM T1,
             LATERAL (SELECT T2.C
                      FROM T2
                      WHERE T1.x = T2.x and T2.k = 5)(+) LV;

The lateral view in query L is merged to yield query M.

M.
        SELECT T1.d, T2.c
        FROM T1, T2
        WHERE T1.x = T2.x (+) and T2.k (+)= 5;

Consider query N, which expresses a left outerjoin in the ANSI join syntax. Currently query N cannot be expressed using the Oracle native left outer join operator.

N.
        SELECT T1.m, T2.n
        FROM T1 LEFT OUTER JOIN T2
        ON (T1.h = 11 and T1.y = T2.y)
        WHERE T1.q > 3;

The query N is converted into query O with a left outer-joined lateral view. The lateral view in O cannot be merged, since the filter on the left table specified in the ON clause must be part of the left outerjoin condition.

O.
        SELECT T1.m, LV.n
        FROM T1,
             LATERAL(SELECT T2.n
                     FROM T2
                     WHERE T1.h = 11 and T1.y = T2.y)(+) LV
        WHERE T1.q > 3;

Consider query P with a disjunction in the outer join condition. Currently N cannot be expressed using the Oracle native left outer join operator.

P.
        SELECT T1.A, T2.B
        FROM T1 LEFT OUTER JOIN T2
        ON (T1.x = T2.x OR T1.Z = T2.Z);

The query P will be converted into Q with a left outer-joined lateral view containing the disjunctive join condition.

Q.
        SELECT T1.A, LV.B
        FROM T1,
             LATERAL (SELECT T2.B
                      FROM T2
                      WHERE T1.x = T2.x OR T1.Z = T2.Z) (+) LV;

ANSI Full Outerjoin

Before Oracle Database 11g Release 1, all ANSI full outerjoins were converted into a UNION ALL query with two branches, where one branch contained a left outerjoined lateral view and the other branch contained a NOT EXISTS subquery. A native support for hash full outerjoin was introduced in Oracle Database 11g Release 1 to overcome this problem. When the native full outerjoin, cannot be used, Oracle reverts to the pre-11gR1 strategy.

Consider query R, which specifies an ANSI full outerjoin.

R.
        SELECT T1.c, T2.d
        FROM T1 FULL OUTER JOIN T2
        ON T1.x = T2.y;

Before Oracle Database 11g Release 1, Oracle would internally convert query R into S.

S.
        SELECT T1.c, T2.d
        FROM T1, T2
        WHERE T1.x = T2.y (+)
        UNION ALL
        SELECT NULL, T2.d
        FROM T2
        WHERE NOT EXISTS
              (SELECT 1 FROM T1 WHERE T1.x = T2.y);

With the native support of hash full outerjoin, R will be simply expressed as query T, where the view, VFOJ, is considered unmergeable.

T.
        SELECT VFOJ.c, VFOJ.d
        FROM (SELECT T1.c, T2.d
              FROM T1, T2
              WHERE T1.x F=F T2.y) VFOJ;

Conversion of Outerjoin into Inner Join

Consider query U. Here the filter predicate on the outer-joined table T2 does not contain the outerjoin operator (+); thus it will be applied after the left outerjoin has taken place. This will result in the elimination of all null appended rows of T2. Hence, Oracle converts the outer join into an inner join.

U.
        SELECT T1.d, T2.c
        FROM T1, T2
        WHERE T1.x = T2.x (+) and T2.y > 5;

The ANSI left outer join query V is equivalent to query U, as the WHERE clause in V is applied after the left outer join is performed based on the condition specified in the ON clause.

V.
        SELECT T1.d, T2.c
        FROM T1 LEFT OUTER JOIN T2
        ON (T1.x = T2.x)
        WHERE T2.y > 5;

Oracle converts the queries U and V into query W with an inner join.

W.
        SELECT T1.d, T2.c
        FROM T1, T2
        WHERE T1.x = T2.x and T2.y > 5;

Q&A

Q1: I do not understand the queries N and O. What is the difference between
   a filter appearing in the ON Clause or a filter appearing in the WHERE
   clause?

A1:  Consider two tables T11 and T22.

T11:
        A   |   B
        1   |   2
        2   |   3
        3   |   5

T22:
        X   |   Y
        7   |   2
        8   |   4
        9   |   4

The following ANSI left outer join query N' involving the tables T11 and T22 will return three rows, since the filter, which always fails, is part of the join condition. Although this join condition, which comprises both the predicates in the ON clause, always evaluates to FALSE, all the rows of the left table T11 are retained in the result.

N'.
SELECT *
FROM T11 LEFT OUTER JOIN T22
    ON (T11.A > 9 and T11.B = T22.Y);

       A           B           X           Y
    ------ ---------- ---------- ---------
       1            2
       2            3
       3            5

However, if the filter, T11.A > 9, is moved to the WHERE clause,
the query will return zero rows.

Q2:  Is the outer to inner join conversion a new feature?

A2:  No. This feature has been available since Release 7.

Q3:  Has native full outer join been made available in versions prior to 11gR1?

A3: Yes. It is available in 10.2.0.3 and 10.2.0.4, but not by default.

Join the discussion

Comments ( 3 )
  • Rajeshwaran, Jeyabal Wednesday, November 2, 2011

    Q3: Has native full outer join been made available in

    versions prior to 11gR1?

    A3: Yes. It is available in 10.2.0.3 and 10.2.0.4, but not by

    default

    so what hint/changes should I need to use to make this Native full outer join available in 10.2.0.3? If possible can you show us a demo?

    rajesh@ORA10GR2> select t1.x, t2.y

    2 from t1 full outer join t2

    3 on (t1.x = t2.x)

    4 /

    Elapsed: 00:00:00.01

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 2841162349

    -----------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    -----------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 4 | 104 | 13 (8)| 00:00:01 |

    | 1 | VIEW | | 4 | 104 | 13 (8)| 00:00:01 |

    | 2 | UNION-ALL | | | | | |

    |* 3 | HASH JOIN OUTER | | 3 | 117 | 7 (15)| 00:00:01 |

    | 4 | TABLE ACCESS FULL| T1 | 3 | 39 | 3 (0)| 00:00:01 |

    | 5 | TABLE ACCESS FULL| T2 | 3 | 78 | 3 (0)| 00:00:01 |

    |* 6 | HASH JOIN ANTI | | 1 | 39 | 7 (15)| 00:00:01 |

    | 7 | TABLE ACCESS FULL| T2 | 3 | 78 | 3 (0)| 00:00:01 |

    | 8 | TABLE ACCESS FULL| T1 | 3 | 39 | 3 (0)| 00:00:01 |

    -----------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    3 - access("T1"."X"="T2"."X"(+))

    6 - access("T1"."X"="T2"."X")

    Note

    -----

    - dynamic sampling used for this statement


  • guest Friday, March 23, 2012

    Good afternoon, I was told that in new versions of oracle the outer join would not be supperted by using the (+) in none of the ways shown in this article.

    I want to know if it is true, because if we have to change the way we write the querys on our applications is better to start now, or even if it will be supported is better not to use the (+) and use the left or right join sintaxis?

    We are working on 10g

    Thanks a lot.


  • David L Wallace Tuesday, May 21, 2019
    This is the best Oracle article I've ever read on outer join functionality. Thanks for posting it...
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.