Cross Join | SQL Joins | Teradata Vantage - 17.05 - Cross Join - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

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

SQL supports unconstrained joins (or cross joins), which are joins for which a WHERE clause relationship between the joined tables is not specified. The result of an unconstrained join is also referred to as a Cartesian product.

The collection of all such ordered pairs formed by multiplying each element of one relation with each element of a second relation is the same result obtained by performing a cross join between two tables: the join is the product of the rows in each table that is joined.

Reasons for Performing a Cross Join

Concatenating each row of one table with every row of another table rarely produces a useful result. In general, the only reason to request a cross join is to write performance benchmarks. Real world applications of cross joins are essentially nonexistent.

Before performing a cross join, you should address the following considerations:

  • Why you need to execute such a wide ranging and uninformative operation
  • How expensive (with respect to resource consumption) a cross join can be

For example, a cross join of two tables, table_a and table_b, each with a cardinality of 1,000 rows, returns a joined table of 1 million rows. Cross joins can easily abort transactions by exceeding user spool space limits.

Specifying a Cross Join

If you want to return a Cartesian product that is not an outer join of two or more tables, you can write the following SELECT statement:

     SELECT ...
     FROM table_a
     CROSS JOIN table_b;

Because the reserved word sequence CROSS JOIN is optional, you could also write the following SELECT statement that accomplishes the same result:

     SELECT ...
     FROM table_a,table_b;

The first form, with the explicit CROSS JOIN syntax, more clearly indicates that a cross join is the intent of the query.

If table_a contains five rows and table_b contains three rows, then the Cartesian product is 3 x 5, or 15. An unconstrained join on these tables results in 15 rows being returned.