Cartesian Product in DBMS: Definition and Examples

In DBMS, the Cartesian Product is an operation that returns all possible combinations of rows from two or more tables. It pairs each row of the first table with every row of the second table, producing a result set whose size is the product of the number of rows in the input tables.

How It Works

Imagine you have two lists: one with colors and another with shapes. The Cartesian Product pairs each color with every shape, creating all possible color-shape pairs. In databases, this means combining every row from one table with every row from another.

This operation is like making all possible combinations without any filtering. If the first table has 3 rows and the second has 4, the result will have 3 × 4 = 12 rows. It is often the first step before applying conditions to filter meaningful pairs.

Example

This example shows the Cartesian Product of two tables: Colors and Shapes.

sql

SELECT Colors.Color, Shapes.Shape
FROM Colors, Shapes;

Output

Color | Shape
------|-------
Red   | Circle
Red   | Square
Red   | Triangle
Blue  | Circle
Blue  | Square
Blue  | Triangle

When to Use

Cartesian Product is useful when you want to generate all possible combinations between two sets of data. For example, creating a schedule by pairing all employees with all shifts, or combining all products with all possible discounts.

However, it should be used carefully because it can produce very large results if tables are big. Usually, it is combined with WHERE clauses to filter the pairs to meaningful matches, such as in JOIN operations.

Key Points

• The Cartesian Product pairs every row of one table with every row of another.
• Result size equals the product of the number of rows in the input tables.
• It can create very large result sets if tables are large.
• Often used as a base for JOIN operations with filtering conditions.

Key Takeaways

Cartesian Product returns all possible row combinations from two tables.

Result size is the multiplication of row counts from input tables.

It is useful for generating combinations but can produce large outputs.

Usually combined with filters to get meaningful data in joins.