What is a Cartesian Join in SQL?

The CARTESIAN JOIN in SQL, also known as CROSS JOIN is a type of SQL join where the Cartesian product from two or more tables is obtained using the JOIN functions in SQL. It is a type of join where we see the number of rows in the result set as the product of the number of rows from each table. Cartesian join is the join condition where each row of one of the tables is getting joined with each row of another table which explains that it takes into account all the possible combinations of rows present in the two tables.

It is quite interesting to note that the CARTESIAN JOIN behaves like an INNER JOIN when the join column or the WHERE clause signifies the condition holds TRUE. When the matching column or the WHERE clause is not specified then the join behaves as CARTESIAN JOIN else it is an INNER JOIN.

For example, if we have two tables with one having eight rows and the other having six rows then the result set of the cartesian join in SQL will be 8X6=24 rows.

Syntax:

Explanation: Shown above is the syntax to write the CROSS JOIN between the two tables that is, table1 and table2. We have not used the ON or WHERE condition as we want to specify the join, to take all the possible combinations.

The below diagram illustrates the CARTESIAN JOIN :

Why use Cartesian Join in SQL?

While talking about the use case of Cartesian join in SQL, we need to know that whenever we are dealing with cartesian join SQL we see a large number of rows obtained in the result set. So, in the applications where we have the requirement of obtaining the result set which includes all the rows (all combinations) we should go with the cartesian join SQL. We must always try to use the cartesian join SQL when we have a high number of rows.

We need to take note here that while dealing with SQL in general, we are preparing our dataset that needs to be crisp and clear. The result obtained after the SQL transformation must always help to generate meaningful insights. Hence, we use SQL to obtain only those rows that fit our Specific condition and we obtain the result set containing only the required number of rows.

The Cartesian JOIN in SQL should be used when we deal with the following scenarios:

• When the JOIN clause or condition is omitted. This means, that when the JOIN condition is not defined, then it tells to show the output as the conjugation of all the possible combinations
• When the JOIN clause or condition is not valid. It can also happen that the JOIN condition is not getting satisfied. As a result, it fetches all the possible combinations in the output.
• When we require all the rows of the first table to be concatenated to all the rows of the second table, we can make use of CROSS JOIN to understand all the combinations that can be obtained.

So, we must mostly avoid cartesian join SQL and instead go for INNER JOIN as it helps to reduce and generate the crisp number of rows according to the condition specified. Hence, making it easy for the analyst to draw insights from the same. Concerning the performance, the cartesian join in SQL is heavy and costly as it tries to fetch all the various possible combinations from the two tables. While running the cartesian join SQL, the system tends to consume more resources and can lead to performance issues that can be cost-heavy.

The below diagram shows how the CARTESIAN JOIN helps get all the rows but can affect the performance negatively.

What is a Cartesian Product in SQL?

A Cartesian product in SQL can be defined as one that was derived from the set theory that we all might have studied in mathematics. It also finds its significance in SQL database manuals. Now let us jump in to find out What a cartesian product is in SQL along with an example.

The basic definition for the Cartesian product in SQL can be - A cartesian product in SQL between two sets says S and T, is the resultant set where we will see all the ordered pairs that are, s is in S and t is in T. It is denoted as S X T.

The Cartesian product of two tables forms a new table where we see a cross product of the number of rows in each of the two tables as the number of rows in the final result table. For example, Let's consider the below-mentioned two tables.

We see that there are two tables with three rows each. Now, if we want to calculate the number of rows in the final table after the cartesian join SQL, we will be getting it as 3 X 3 = 9 rows in the final set as can be seen. Therefore, we see that each row of the first table gets joined with each row of the second table and we get the multiplication result from the two tables resulting in all the possible ordered pairs being obtained from the original tables.

A large amount of computational operations is involved when dealing with the Cartesian product which makes the cartesian join SQL redundant as it affects the performance and is cost-heavy as well.

How to Implement a Cartesian Product in SQL?

Looking at the above diagram, we see two tables getting merged as one offering all the possibilities (at max) after the implementation of Cartesian Product in SQL. Now, let us dive into how we use the logic to generate the Cartesian Product in SQL.

We can start implementing a Cartesian Product in SQL with the help of the CROSS JOIN operator which helps to return the output as the cross product of the two tables. As we saw above, we have two tables that state the color and the size values. As no JOIN condition was explicitly mentioned, we get a total of 8 rows in the resultant set.

We have the applications of the CROSS JOIN method in many situations. For example, when we need to get the full salary data for the employees for a month then we can use this method and implement the Cartesian Product in SQL to draw the insights.

There is also the time when we make use of the Cartesian Product in SQL when the database contains tables that have only one row stored (e.g., company name) then in that case we can make use of cartesian join SQL to any query.

Example of Cartesian Join in SQL?

Example 1

Suppose we have two tables Student and Student_Course as shown below. Now, we want to select the NAME and AGE from the Student table, and the corresponding COURSE_ID from the Student_Course table. Let us implement the cartesian join SQL in order to obtain the output having the total rows 4 * 4 = 16.

TABLE A - Student:

TABLE B - Student_Course:

SQL QUERY:

OUTPUT:

Explanation: As can be seen above, we are getting a total of 16 rows after the implementation of CROSS JOIN. Each of the NAME columns of the first table is getting joined to the COURSE_ID column of the second table, which explains how the CROSS JOIN helps in obtaining all the possible combinations.

Example 2

Suppose we have two tables Customer and Orders as shown below. Now, we want to select the ID, NAME from the Customer table, and AMOUNT, and DATE from the corresponding Orders table. Let us implement the cartesian join SQL in order to obtain the output having the total rows 7 * 4 = 28.

TABLE A - Customer:

TABLE B - Orders:

SQL QUERY:

OUTPUT:

Explanation: As can be seen above, we are getting a total of 28 rows after the implementation of CROSS JOIN. Each of the NAME columns of the first table is getting joined to the AMOUNT column of the second table, which explains how the CROSS JOIN helps in obtaining all the possible combinations.

Learn More about Cartesian join in SQL

If you would love to explore more about the Cartesian join in SQL, then do not forget to check the article which explains the CROSS JOIN with elaborate examples in detail.