Crossjoins

Crossjoins, or cross joins, refer to a relational operator that computes the Cartesian product of two input sets. In relational databases, a cross join returns every possible pairing of rows from the two input tables, producing a result set whose row count equals the product of the input row counts. There is no join predicate or condition in a cross join.

Syntax and equivalence: The operation can be written as SELECT ... FROM TableA CROSS JOIN TableB. In many systems, listing the tables separated by a comma in the FROM clause (SELECT ... FROM TableA, TableB) yields the same result as a cross join, but CROSS JOIN makes the intent explicit and can affect optimization plans.

Example: If A has two rows (1, 'Alice') and (2, 'Bob'), and B has three rows ('red'), ('green'), ('blue'), the query SELECT A.id, A.name, B.color FROM A CROSS JOIN B returns six rows, pairing each A row with each B color.

Use and considerations: Cross joins are rarely used to return data directly; they are often a step toward forming all combinations for testing, creating a denormalized product, or serving as the base before applying a filter (WHERE) to obtain an inner-join-like subset. Because the result size grows multiplicatively, cross joins can be expensive and should be used with care, particularly with large tables.

Variations: In some data-analysis contexts such as MDX, CrossJoin is a function that combines two sets into all possible tuples, serving a similar, but functionally distinct, purpose.