OUTER JOIN Hints - Teradata

The easiest way to begin writing an OUTER JOIN is to:

  1. Start with an INNER JOIN and convert to an OUTER JOIN. Once the INNER JOIN is working, change the appropriate INNER descriptors to LEFT OUTER, RIGHT OUTER or FULL OUTER join based on the desire to include the exception rows. Since INNER and OUTER joins can be used together, one join at a time can be changed to validate the output. Use the join diagram below to convert the INNER JOIN to an OUTER JOIN.
  2. For joins with greater than two tables, think of it as: JOIN two tables at a time. It makes the entire process easier by concentrating on only two tables instead of all tables. The optimizer will always join two tables, whether serially or in parallel and it is smart enough to do it in the most efficient manner possible.
  3. Don't worry about which tables you join first. The optimizer will determine which tables should be joined first for the optimal plan.
  4. The WHERE clause, if used in an OUTER JOIN to eliminate rows.
    • It is applied after then join is complete, not when rows are read like the Inner Join.
    • It should reference columns from the outer table. If columns from the Inner table are referenced in a WHERE clause, the optimizer will most likely perform a merge join (INNER) for efficiency. This isactually an INNER JOIN operation and can be seen in the EXPLAIN output.Join Diagram

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics