What is a Cartesian Product in SQL?

A Cartesian product is a type of join operation in SQL (Structured Query Language) that combines every row from one table with every row from another table. It is also known as a cross join. This results in a new table with a row for each possible combination of rows from the two tables. It is named after the French mathematician and philosopher René Descartes, who is credited with the development of the Cartesian coordinate system.

For example, consider two tables: Table A with columns A1 and A2, and Table B with columns B1 and B2. A Cartesian product of these tables would result in a new table with columns A1, A2, B1, and B2, and every possible combination of rows from Table A and Table B.

The syntax for performing a Cartesian product in SQL varies depending on the database management system being used. In most systems, the syntax would be similar to the following:

SELECT *
FROM table_a
CROSS JOIN table_b

It is important to note that Cartesian products can produce very large tables. They should be used with caution. It is often necessary to include a WHERE clause to limit the size of the resulting table.

Inadvertent Cartesian Products in SQL

It is possible to inadvertently create a Cartesian product in SQL if your JOIN conditions are incorrect. SQL JOINs are used to retrieve data from multiple tables in SQL. Any situation where your JOIN clause evaluates to “true” for every record in both tables will result in a Cartesian product.

For example, consider the following SQL statement:

SELECT *
FROM customers
INNER JOIN users ON customers.user_id = users.id

This statement will perform an inner join between customers and users. The resulting table will only include rows where the customers.user_id matches the value in users.id.

Now consider the following SQL statement:

SELECT *
FROM customers
INNER JOIN users ON 1 = 1;

Because 1 is always equal to 1, our JOIN clause will always be true for every row in both tables. This will result in an inadvertent Cartesian Product in our query. You must be careful to avoid these. On large SQL tables, inadvertently retrieving a Cartesian Product can result in an extremely large data set. This may cause the query to run for a long time. It could also lead to the database server running becoming slow as a result.

In summary, a Cartesian product in SQL is a type of join operation. It combines every row from one table with every row from another table. This results in a result set with a row for each possible combination of rows from the original tables. You can learn more about Cartesian Products and the CROSS JOIN functionality in our online beginner SQL course.