When CROSS Join Will Be Useful

What is CROSS Join and in which scenario we will be using Cross Join?

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product. If WHERE clause is used with CROSS JOIN, it functions like an INNERJOIN.

So, Cross join is used to return all records where each row from first table is combined with each row in second table.

Now, the Question is when we will be using Cross Join. Basically, idea behind joining tables is to have relationship with some Key columns. Consider a scenario where we don’t have Key columns between TABLES and still there is a situation where we need data from both the tables in required format. We will end up doing Cross Join. There could be multiple situations in which we need to use CROSS Join.

In this post, we will see this with one Example- When we are maintaining Data of a Restaurant into Two Tables- Let’s say PRODUCT and SUB_PRODUCT.

PRODUCT_ID	PRODUCT_NAME	PRICE
101	Regular PIZZA	160
102	Medium PIZZA	220
103	Large PIZZA	330
104	Cheese Burst	420
105	Double Cheese Burst	480

SUBPRODUCT_ID	SUBPRODUCT_NAME	PRICE
1	Cold Drink	60
2	Cake	80
3	Garlic Bread	70

Now, think about a scenario where you will be providing customer this information in more easy way with combination of Product from First Table and Sub Product with their sum price.

So, My OUTPUT should look like below:

PRODUCT_NAME	SUBPRODUCT_NAME	COMBINED_PRICE
Regular PIZZA	Cold Drink	220
Medium PIZZA	Cold Drink	280
Large PIZZA	Cold Drink	390
Cheese Burst	Cold Drink	480
Double Cheese Burst	Cold Drink	540
Regular PIZZA	Cake	240
Medium PIZZA	Cake	300
Large PIZZA	Cake	410
Cheese Burst	Cake	500
Double Cheese Burst	Cake	560
Regular PIZZA	Garlic Bread	230
Medium PIZZA	Garlic Bread	290
Large PIZZA	Garlic Bread	400
Cheese Burst	Garlic Bread	490
Double Cheese Burst	Garlic Bread	550

PRACTICAL:

CREATE TABLE PRODUCT
(
PRODUCT_ID NUMBER,
PRODUCT_NAME VARCHAR2(80),
PRICE NUMBER
);

INSERT INTO PRODUCT VALUES(101,'Regular PIZZA',160);
INSERT INTO PRODUCT VALUES(102,'Medium PIZZA',220);
INSERT INTO PRODUCT VALUES(103,'Large PIZZA',330);
INSERT INTO PRODUCT VALUES(104,'Cheese PIZZA',420);
INSERT INTO PRODUCT VALUES(105,'Double Cheese Burst',480);
COMMIT;

CREATE TABLE SUB_PRODUCT
(
SUBPRODUCT_ID NUMBER,
SUBPRODUCT_NAME VARCHAR2(80),
PRICE NUMBER
);

INSERT INTO SUB_PRODUCT VALUES(1,'Cold Drink',60);
INSERT INTO SUB_PRODUCT VALUES(2,'Cake',80);
INSERT INTO SUB_PRODUCT VALUES(3,'Garlic Bread',70);
COMMIT;

Final Query:

SELECT 
P.PRODUCT_NAME, 
S.SUBPRODUCT_NAME, 
(P.PRICE+S.PRICE) AS COMBINED_PRICE
From PRODUCT P CROSS JOIN SUB_PRODUCT S;

The above Query will give the Output As below: