Guidelines for Using Outer Joins - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Outer joins, when used properly, provide additional information from a single query that would otherwise require multiple queries and steps to achieve. However, the proper use of outer joins requires training and experience because “common sense” reasoning does not always apply to formulating an outer join query in a manner that is not only syntactically correct, but also returns an answer that is correct for the business question.

To ensure that you get the right answers to your business questions using outer joins, refer to these steps.

  1. Understand the question you are trying to answer and know the demographics of your tables.

    You should have a good idea of what the answer set should look like before you begin. See Single Table Cardinality Estimate and Inner Join Cardinality Estimate.

  2. Write the query, keeping in mind the proper placement of join and search conditions.
    PLACE these conditions … IN this clause …
    join ON
    search condition predicates for inner table ON
    search condition predicates for outer table WHERE
  3. Always EXPLAIN the query before performing it.

    Look for the words outer join in the EXPLAIN text. If they are not there, then the Optimizer did not produce an outer join plan.

  4. Perform the query and compare the result with your expectations.
    IF the answer set … THEN …
    matches your expectations it is probably correct.
    does not match your expectations check the placement of the selection criteria predicates in the ON and WHERE clauses of your outer join.

For information about the various types of join methods the Optimizer might use in responding to your requests, see Chapter 3, “Join Planning and Optimization,” in SQL Request and Transaction Processing .