Outerjoins in Oracle

Since release 6, Oracle has supported a restricted form of left outerjoin, which uses Oracle-specific syntax. In 9i, Oracle 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 11gR1 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 11gR1 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 11gR1, 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.

Comments:

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

Posted by Rajeshwaran, Jeyabal on November 02, 2011 at 09:29 AM PDT #

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.

Posted by guest on March 23, 2012 at 10:14 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today