SQL Joins | Data Manipulation Language | Teradata Vantage - 17.05 - Joins - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

A join is an action that projects columns from two or more tables into a new virtual table. Vantage 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

In queries that join the tables, you can specify four basic types of join:
  • 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.

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.

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:
  • 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 Teradata Vantage™ - Database Design, B035-1094.

  • It is often difficult to code an outer join correctly.

Joining Tables and Views That Have Row-Level Security Constraints

Vantage 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 Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100 and CREATE CONSTRAINT in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .