Cross Join | SQL Joins | Teradata Vantage - 17.05 - Cross Join - 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)

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.