Cartesian Join in SQL

The CARTESIAN is also called CROSS JOIN. And in a CARTESIAN JOIN, there exists a join for every row of a table to every row of some other table. It usually occurs when the matching column isn’t specified on when the WHERE condition isn’t specified.

In this article, we will take a look at the Cartesian or Cross Join in SQL according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

What is Cartesian Join in SQL?
- - Syntax
- Example
FAQs

What is Cartesian Join in SQL?

A CROSS JOIN or CARTESIAN JOIN returns the Cartesian product of an available set of records from multiple joined tables. Thus, this join would equate to an inner join in which the join-condition always evaluates to either True or wherever the join-condition is not present in the statement.

Syntax

Here is the basic syntax of the Cross Join or the Cartesian Join:

SELECT table_a.column_a, table_b.column_b…

FROM table_a, table_b [, table_c ]

Now let us take a look at an example.

Example

We will now consider the two tables given as follows:

Table 1: The CLIENTS Table:

ID	NAMETAG	ADDRESS	AGE	WAGE
1	Rimona	Ahmedabad	32	2000.00
2	Kujo	Delhi	25	1500.00
3	Kiara	Kota	23	2000.00
4	Chilly	Mumbai	25	6500.00
5	Helen	Bhopal	27	8500.00
6	Kitty	MP	22	4500.00
7	Mindy	Indore	24	10000.00

Table 2: The ORDERS Table:

OID	DATE	CLIENT_ID	TOTAL
102	2019-10-18 00:00:00	3	3000
100	2019-10-18 00:00:00	3	1500
101	2019-11-10 00:00:00	2	1560
103	2018-05-10 00:00:00	4	2060

Now, we will join the given two tables as follows using the LEFT JOIN:

SQL> SELECT ID, NAMETAG, AMOUNT, DATE

FROM CLIENTS, ORDERS;

As a result, it would produce the result as follows:

ID	NAME	AMOUNT	DATE
1	Rimona	3000	2019-10-18 00:00:00
1	Rimona	1500	2019-10-18 00:00:00
1	Rimona	1560	2019-11-10 00:00:00
1	Rimona	2060	2018-05-10 00:00:00
2	Kujo	3000	2019-10-18 00:00:00
2	Kujo	1500	2019-10-18 00:00:00
2	Kujo	1560	2019-11-10 00:00:00
2	Kujo	2060	2018-05-10 00:00:00
3	Kiara	3000	2019-10-18 00:00:00
3	Kiara	1500	2019-10-18 00:00:00
3	Kiara	1560	2019-11-10 00:00:00
3	Kiara	2060	2018-05-10 00:00:00
4	Chilly	3000	2019-10-18 00:00:00
4	Chilly	1500	2019-10-18 00:00:00
4	Chilly	1560	2019-11-10 00:00:00
4	Chilly	2060	2018-05-10 00:00:00
5	Helen	3000	2019-10-18 00:00:00
5	Helen	1500	2019-10-18 00:00:00
5	Helen	1560	2019-11-10 00:00:00
5	Helen	2060	2018-05-10 00:00:00
6	Kitty	3000	2019-10-18 00:00:00
6	Kitty	1500	2019-10-18 00:00:00
6	Kitty	1560	2019-11-10 00:00:00
6	Kitty	2060	2018-05-10 00:00:00
7	Mindy	3000	2019-10-18 00:00:00
7	Mindy	1500	2019-10-18 00:00:00
7	Mindy	1560	2019-11-10 00:00:00
7	Mindy	2060	2018-05-10 00:00:00

FAQs

What is a Cartesian join in SQL?

Why do we use Cartesian join in SQL?

The CROSS JOIN in SQL is used when we want to combine every row of the first available table along with every row of the second table. The cross join is also called the Cartesian join because it returns the Cartesian product of a set of rows that we get from the joined tables.

How can we avoid Cartesian join?

In an inner join, we can avoid a Cartesian product by providing a join condition that is NOT true in the case of all the row combinations of the tables that are joined.

What is the difference between full join and cross join?

A cross join would produce a Cartesian product that is present between the two tables that returns all the possible combinations of all the available rows. It does not consist of the ON clause because we are just joining everything available to us to something else. On the other hand, a full outer join is like a combination of a right outer join and a left outer join.

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus, GATE Previous Year Question Paper, and more.

Also Explore,