17.05 - Guidelines for Using Outer Joins - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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 the correct 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.

    Form a hypothesis of the answer set you expect 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.
    Condition 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 the EXPLAIN text does not include outer join, the Optimizer did not produce an outer join plan.

  4. Perform the query and compare the result with your expectations.
    Answer Set Description
    Matches your expectations. Answer set 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 uses in responding to requests, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.