An outer join extends the result of a equi join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
Outer Join are not evil. When used appropriately, they are no faster or slower than a inner join (or regular).
select t.*, t2.c1, t3.c3
from t, t2, t3
where t.key1 = t2.key1 (+)
and t.key2 = t3.key2 (+)
Is the same with a regular join transformation that :
select t.*, (select c1 from t2 where t2.key1 = t.key1) c1,
(select c2 from t3 where t3.key1 = t.key2) c2
from t
Using outer joins can be very useful, but you should be aware of the following performance and implementation issues:
More rows are returned and some databases will not use indexes when outer joins are involved, so large amounts of data could slow query performance.
for tables after the outer join (RDBMS dependent)
You should verify how your target RDBMS processes outer joins to avoid incomplete query paths after the original outer join.
For example, in a Microsoft Access database, all one-to-many joins following the outer join in the join path must also be defined as outer joins. If not, the original outer join will be ignored by the resulting query.
Not all databases allow control over outer joins in the WHERE clause. This is necessary when using a self restricting join.
For example, a self restricting join 'TYPE_CODE=10', could return all rows where TYPE=10 or Type is NULL, as TYPE=10 will never be true when the type code is NULL, whereas NULL values are generated by the outer join.