Guidelines for Using Outer Joins - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.