15.00 - Joins - Teradata Database

Teradata Database SQL Data Manipulation Language

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)


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:

  • 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:

  • “Inner Joins” on page 247
  • “Ordinary Inner Join” on page 248
  • “Cross Join” on page 251
  • “Self‑Join” on page 252
  • 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:

  • “Inner Joins” on page 247
  • “Ordinary Inner Join” on page 248
  • 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” on page 253
  • “Left Outer Join” on page 265
  • “Right Outer Join” on page 267
  • “Full Outer Join” on page 269
  • 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:

  • “Coding ON Clauses for Outer Joins” on page 274
  • “Coding ON Clauses With WHERE Clauses for Outer Joins” on page 277
  • “Rules for Coding ON and WHERE Clauses for Outer Joins” on page 280
  • “Outer Join Case Study” on page 282
  • 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.