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 w3wiki.
Query:
CREATE DATABASE w3wiki
Output:
Step 2: To start working with our database we need to use the database. For this we use the below command.
Query:
USE w3wiki;
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 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 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:
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.