Nested Join Examples | Join Planning/Optimization | Teradata Vantage - 17.10 - Nested Join Examples - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
English (United States)

Circumstances for the Nested Join Examples

This section provides the partial EXPLAIN outputs for the same two-table join under the following circumstances:
  • No nested join
  • A nested join where the USI for one table is joined on a column from the other table.

Table Definitions

For these examples, assume the following table definitions:

Table Name Column 1 Name Column 2 Name Primary Index Name Unique Secondary Index Name Number of Rows
table_1 NUPI        y_1 NUPI none defined                   2
table_2 NUPI        USI_2 NUPI          USI_2     1,000,000

Test Query

The following request is tested against this database, both using and without using nested join:

FROM table_1,table_2
WHERE y_1 = USI_2;