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.
- 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.
- 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 - 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.
- 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 “Join Planning and Optimization” in Teradata Vantage™ SQL Request and Transaction Processing, B035-1142.