A join is an action that projects columns from two or more tables into a new virtual table. Teradata Database supports joins of as many as 128 tables and single-table views per query block.
A join can also be considered an action that retrieves column values from more than one table.
See “Outer Join Relational Algebra” for definitions of the relational algebra terms project, restrict, and product.
Join Varieties
- Natural
- Θ (theta)
- Inner
- Outer
You can combine join types, for example, make natural inner and outer joins as well as Θ inner and outer joins.
Natural and Theta Joins
The natural join and the Θ (theta) join are the two basic types of join.
The natural join is an equality join made over a common column set with matching column names such as a primary index or primary key-foreign key relationship that is expressed in a WHERE clause equality condition. For example,
... WHERE a.custnum = b.custnum ...
The Θ join is a more general type of join where you can use conditions such as greater than (>), greater than or equal to ( ≥), less than (<), less than or equal to ( ≤), in addition to equality (=). For example,
... WHERE a.custnum > b.custnum ...
If the Θ operator is equality (=), then the join is considered an equijoin. In certain cases, the natural join and the equijoin are identical. Because Teradata SQL does not support the ANSI SQL-2011 NATURAL JOIN syntax, it does not recognize a practical difference between natural and equijoins, which are considered equivalent. While both joins are made over an equality condition, the column names on which tables are joined need not match in an equijoin, while they must match in a natural join.
SQL also supports two other special join cases: the cross join, or Cartesian product, and the self-join.
Note that intersection and Cartesian product are special cases of the join operation. See “INTERSECT Operator” in SQL Functions, Operators, Expressions, and Predicates .
Inner and Outer Joins
The inner join projects only those rows that have specific commonality between the joined tables. Because the inner join does not include rows that have no counterpart in the other table, it is sometimes said to lose that information.
The outer join is meant to provide a method for regaining the "lost" information the inner join does not report. The outer join is an extended inner join that projects not only those rows that have commonality between the joined tables, but also those rows that have no counterpart in the other relation.
Depending on how you write an outer join, it can project the inner join rows plus any of the following: the nonmatching rows of the left table, the nonmatching rows of the right table, or the nonmatching rows from both. The attributes of the complementary row sets of an outer join are represented in the result set by nulls.
Outer joins are somewhat controversial for several reasons:
- There are some formal complications involved in deriving outer joins. For example, while it is true that the inner natural join is a projection of the inner equijoin, it is not true that the outer natural join is a projection of the outer equijoin.
- The result of an outer join can be very difficult to interpret, if only because nulls are used to represent two very different things: the standard "value unknown" meaning and the empty set, representing the attributes of the outer row set.
For more information about issues with nulls and the outer join, see Database Design .
- It is often difficult to code an outer join correctly. For more information about coding outer joins, see:
Joining Tables and Views That Have Row-Level Security Constraints
Teradata Database supports joining tables and views that have row-level security constraints if the tables or views have the same row-level security constraints. Otherwise, the system returns an error.
For more information about row-level security constraints, see Security Administration and “CREATE CONSTRAINT” in SQL Data Definition Language Detailed Topics .