Joins
Definition of the SQL Join
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” on page 263 for definitions of the relational algebra terms project, restrict, and product.
Join Varieties
In queries that join the tables, you can specify four basic types of join:
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.
For more detailed information about these join types, see the following topics:
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.
For more information about inner joins, see:
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.
For more information about the outer join and its various types, see:
Outer joins are somewhat controversial for several reasons:
For more information about issues with nulls and the outer join, see Database Design.
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.