Cartesian Join

In SQL, a Cartesian Join is also called a Cross Join, it performs the cartesian product of records of two or more joined tables. A Cartesian product matches each row of one table to every other row of another table, only when the WHERE condition is not specified in the query. In case the WHERE condition is specified then the Cartesian Join works as an Inner Join.

Syntax of Cartesian Join

With the WHERE clause

SELECT *
FROM table 1 
CROSS JOIN  table 2
WHERE condition

Without the WHERE Clause

SELECT table1.column1 , table2.column2...
FROM table1, table2...
CROSS JOIN table2;

How to Perform Cartesian Join on Tables?

To Perform follow the given steps :

Step 1: To start with first we need to create a Database. For creating a new database use the below command. As an example, we are creating a new database GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: To start working with our database we need to use the database. For this we use the below command.

Query:

USE GeeksForGeeks;

Output:

Step 3: Now we need to create tables in this database so as an example we are creating two tables. One is Students and other one is Library. Use the below commands to create the tables.

Query:

CREATE TABLE STUDENTS(
ID INT,
NAME VARCHAR(20),
BRANCH VARCHAR(20),
NUMBER INT);

CREATE TABLE LIBRARY(
BOOK_ID INT,
BOOK_NAME VARCHAR(20),
ISSUED_ON DATE,
DUE_DATE DATE);

To view the structure of both tables we use the DESC command.

Query :

DESC STUDENTS;

DESC LIBRARY;

Step 3_1

Step 4: Now to perform Cartesian Join first we will insert some data in the rows of both STUDENTS and LIBRARY table.

Query:

INSERT INTO STUDENTS VALUES(1,'SURYANSH JOHARI','CS',984012);
INSERT INTO STUDENTS VALUES(2,'AMAN SHARMA','IT',771346);
INSERT INTO STUDENTS VALUES(3,'DEV VERMA','ME',638587);
INSERT INTO STUDENTS VALUES(4,'JOY SMITH','CE',876691);
INSERT INTO STUDENTS VALUES(5,'CHARLES GATTO','EE',997679);

INSERT INTO LIBRARY VALUES(121,'RD SHARMA','2023-01-01','2023-01-08');
INSERT INTO LIBRARY VALUES(236,'GATE CRACKER','2023-02-02','2023-02-09');
INSERT INTO LIBRARY VALUES(352,'MORRIS MANO','2023-03-03','2023-03-10');
INSERT INTO LIBRARY VALUES(970,'NK PUBLICATIONS','2023-04-04','2023-04-11');
INSERT INTO LIBRARY VALUES(648,'BIG BANG THEORY','2023-05-05','2023-05-12');

Output:

Step 4

Step 5: After data has been stored in both the tables now we can perform the Cartesian Join on both the tables. As an example here we are doing Cartesian Join with the WHERE clause and finding the records only where NAME of student is Suryansh Johari .

Query:

SELECT *
FROM STUDENTS
CROSS JOIN LIBRARY
WHERE NAME ="SURYANSH JOHARI";

Output:

Step 5

Conclusion

In this article, we have learned how to perform Cartesian Join or Cross Join on tables of a SQL database. Cartesian Join is important when there is a need to obtain all the combinations of rows present in the tables of a database. Cartesian Join with WHERE clause behaves as a Inner join and without WHERE clause it simply performs cartesian product of all rows of all the tables as mentioned in the SQL query.

Frequently Asked Questions

1. What is a Cartesian Join?

Cartesian join is a type of join which combines each row of one table to every other row of another table.

2. What happens when a WHERE condition is specified in Cartesian Join?

In case a WHERE condition is specified then Cartesian Join behaves as a Inner Join.

3. What is the difference between Cartesian Join and Natural Join?

Natural Join joins two tables based on same attribute name and datatypes. Cartesian Join produces cartesian product of two tables .