Cartesian Product

Definition

Cartesian Product (symbol: ×) combines every row of one relation with every row of another, producing ALL possible combinations — if relation A has m rows and relation B has n rows, A × B has m × n rows. Unlike Union/Intersection/Difference, the two relations do NOT need to be union-compatible; they can have entirely different columns (the result simply has all of A's columns plus all of B's columns).

How It Works

sql
SELECT * FROM students CROSS JOIN courses;

(equivalently, in most dialects: SELECT * FROM students, courses; — a comma-separated FROM list with no WHERE/ON condition)

If students has 5 rows and courses has 8 rows, this produces 40 rows — every student paired with every course, regardless of whether that student is actually enrolled in that course. On its own, a Cartesian Product is rarely the FINAL answer to a real question; it's almost always the conceptual STARTING POINT that Selection then filters down to something meaningful — which is exactly how a Theta Join (7.8) is formally defined: Cartesian Product followed by a Selection.

Edge Cases and Pitfalls

• Cartesian Product is the classic accidental-query bug: forgetting a JOIN ... ON condition (or a WHERE condition in old-style comma-join syntax) silently produces a Cartesian Product instead of the intended join — the query still "runs," just returns a huge, mostly-meaningless result instead of erroring.
• The row-count explosion (m × n) can be severe: joining two 10,000-row tables without a proper condition produces 100,000,000 rows — a real, common cause of a query that suddenly "hangs" or exhausts memory.
• Formally, every JOIN variant (Theta, Equi, Natural, Outer) can be understood as "Cartesian Product, then filter/match" — Cartesian Product is the conceptual foundation all the other join operations build on, even though a real database engine never actually materializes the full product for an efficient join.

Key Takeaways

• Cartesian Product (×) pairs every row of one relation with every row of another; result size is the product of the two row counts.
• It requires no shared columns or compatibility between the two relations — unlike Union/Intersection/Difference.
• All join types are formally "Cartesian Product + a filter," which is why understanding this operation clarifies what a join is actually doing underneath.