Did You Know This About Outer Joins in Oracle?

Here is something interesting. Oracle converts this outer join query

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

and its ANSI equivalent

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

into this inner join query

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

That’s according to the Optimizer Development Group blog. They go on to explain:

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.

And here is an interesting thing about ANSI full outer joins:

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.

Their latest blog post about Outerjoins in Oracle is a good read.

Possibly related: