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.