SQL - Outer Join

Data System Architecture


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.

  • Include tuples with no matches in the output
  • Use NULL values for missing attributes

Outer Join are not evil

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

Implementation issues

Using outer joins can be very useful, but you should be aware of the following performance and implementation issues:

Performance can be slower

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.

Incomplete query hierarchy path

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.

Database limitations on the use of outer joins

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.

Recommended Pages
Bobj Outer Join Properties
BOBJ - Outer Join

An outer join is a join that links two tables, one of which has rows that do not match those in the common column of the other table. More ... You define an outer join by specifying which table is...
Data System Architecture
Dimensional Data Operation - Data Densification (sparse to dense data) - Preservation of the dimensions

You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. The densification is also : known as thepreservation...
Obiee Fact Cross Join
OBIEE - Densification with the fact-based fragmentation capabilities

You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. The preservation of the dimensions is is also well known...
Obi Edition
OBIEE - Outer Join

Outer Joins in OBIEE: Outer joins in logical table sources are always included in a query, even if the table source is not used. If possible, create one logical table source without the...
Card Puncher Data Processing
OWB - How to implement a type 2 slowly changing dimension with a hash function ?

Slowly changing dimension is the ability to track change on a record from a data set. How can we implement the type 2 to track the change and implement an history view. The type 2 : add a new record...
Joiner Operator
OWB - Joiner operator

The Joiner operator joins multiple row sets from different sources with different cardinalities, and produces a single output row set. The Joiner operator results in a WHERE clause in the generated SQL...
Key Lookup Inputconnection
OWB - Key lookup operator

Use the Key Lookup operator to lookup (retrieve) data from a set of data (filter,table, view, cube, or dimension ...) For example, use the Key Lookup operator when you define a mapping that loads...
Card Puncher Data Processing
Oracle Database - Effect of a filter on a outer join table

When a filter is set on a outer join table, the result is the same that if a inner/equality join was used. The procedure is as follow: The outer join sql return all values and full in the missing values...
Card Puncher Data Processing
Oracle Database - Join (Operation|Method|Mechanism|Algorithm)

Joins are data operation / method / algorithm in order to match data from more than one table. They all require sorting and then matching aspects of a dataset. There are only three join mechanisms used...
Card Puncher Data Processing
R - Join Data Frame (Merge)

where: the “by” parameters specifies the join column. Default: join by common variable names The by.x and by.y parameters must be used if the matching variables have different names the “all”...

Share this page:
Follow us:
Task Runner