CROSS JOIN
A cross join that does not have a WHERE clause
produces the Cartesian product of the tables involved in the join. The size of
a Cartesian product result set is the number of rows in the first table
multiplied by the number of rows in the second table.
However, if a WHERE clause is added, the cross join
behaves as an inner join.
Suppose you join two tables using CROSS JOIN. The
result set will include all rows from both tables, where each row in the result
set is the combination of the row in the first table with the row in the second
table. This situation happens when you have no relationship between the joined
tables.
Suppose we want to get all member records against all
the movie records, we can use the script shown below to get our desired results.
Example:
To create a table: NAME_LIST
create column table "KABIL_PRACTICE"."NAME_LIST"
(
"Id" integer,
"F_Name" nvarchar(65),
"L_Name" nvarchar(65),
"Movie_Id" integer
);
To Insert a record into a table: NAME_LIST
INSERT INTO "KABIL_PRACTICE"."NAME_LIST" VALUES (1,'Adam','Smith',1);
INSERT INTO "KABIL_PRACTICE"."NAME_LIST" VALUES (2,'Ravi','Kumar',2);
INSERT INTO "KABIL_PRACTICE"."NAME_LIST" VALUES (3,'Susan','Davidson',5);
INSERT INTO "KABIL_PRACTICE"."NAME_LIST" VALUES (4,'Lee','Pong',10);
INSERT INTO "KABIL_PRACTICE"."NAME_LIST" VALUES (4,'Lee','Pong',10);
INSERT INTO "KABIL_PRACTICE"."NAME_LIST" VALUES (5,'Jenny','Adrianna',8);
To create a table: MOVIE_LIST
create column table "KABIL_PRACTICE"."MOVIE_LIST"
(
"Id" integer,
"Title" nvarchar(65),
"Category" nvarchar(65)
);
To Insert a record into a table: MOVIE_LIST
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (1,'ASSASSIN CREED: EMBERS','Animations');
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (2,'Real_Steel(2012)','Animations');
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (3,'Alvin_and_the_Chipmunks','Animations');
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (4,'The Adventures of Tin Tin','Animations');
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (5,'Safe (2012)','Action');
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (6,'Safe House(2012)','Action');
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (7,'GIA','18+');
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (8,'Deadline 2009','18+');
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (9,'The Dirty Picture','18+');
INSERT INTO "KABIL_PRACTICE"."MOVIE_LIST" VALUES (10,'Marley and me','Romance');
The following SELECT statements are equivalent:
select * from "KABIL_PRACTICE"."NAME_LIST", "KABIL_PRACTICE"."MOVIE_LIST";
SELECT * FROM "KABIL_PRACTICE"."NAME_LIST" CROSS JOIN "KABIL_PRACTICE"."MOVIE_LIST";
SELECT * FROM "KABIL_PRACTICE"."NAME_LIST" CROSS JOIN "KABIL_PRACTICE"."MOVIE_LIST"
where "KABIL_PRACTICE"."NAME_LIST"."Id" = "KABIL_PRACTICE"."MOVIE_LIST"."Id" ;
RESULT:
Id
|
F_Name
|
L_Name
|
Movie_Id
|
Id
|
Title
|
Category
|
|
1
|
1
|
Adam
|
Smith
|
1
|
1
|
ASSASSIN
CREED: EMBERS
|
Animations
|
2
|
2
|
Ravi
|
Kumar
|
2
|
1
|
ASSASSIN
CREED: EMBERS
|
Animations
|
3
|
3
|
Susan
|
Davidson
|
5
|
1
|
ASSASSIN
CREED: EMBERS
|
Animations
|
4
|
4
|
Jenny
|
Adrianna
|
8
|
1
|
ASSASSIN
CREED: EMBERS
|
Animations
|
5
|
5
|
Lee
|
Pong
|
10
|
1
|
ASSASSIN
CREED: EMBERS
|
Animations
|
6
|
1
|
Adam
|
Smith
|
1
|
2
|
Real_Steel(2012)
|
Animations
|
7
|
2
|
Ravi
|
Kumar
|
2
|
2
|
Real_Steel(2012)
|
Animations
|
8
|
3
|
Susan
|
Davidson
|
5
|
2
|
Real_Steel(2012)
|
Animations
|
9
|
4
|
Jenny
|
Adrianna
|
8
|
2
|
Real_Steel(2012)
|
Animations
|
10
|
5
|
Lee
|
Pong
|
10
|
2
|
Real_Steel(2012)
|
Animations
|
11
|
1
|
Adam
|
Smith
|
1
|
3
|
Alvin_and_the_Chipmunks
|
Animations
|
12
|
2
|
Ravi
|
Kumar
|
2
|
3
|
Alvin_and_the_Chipmunks
|
Animations
|
13
|
3
|
Susan
|
Davidson
|
5
|
3
|
Alvin_and_the_Chipmunks
|
Animations
|
14
|
4
|
Jenny
|
Adrianna
|
8
|
3
|
Alvin_and_the_Chipmunks
|
Animations
|
15
|
5
|
Lee
|
Pong
|
10
|
3
|
Alvin_and_the_Chipmunks
|
Animations
|
16
|
1
|
Adam
|
Smith
|
1
|
4
|
The
Adventures of Tin Tin
|
Animations
|
17
|
2
|
Ravi
|
Kumar
|
2
|
4
|
The
Adventures of Tin Tin
|
Animations
|
18
|
3
|
Susan
|
Davidson
|
5
|
4
|
The
Adventures of Tin Tin
|
Animations
|
19
|
4
|
Jenny
|
Adrianna
|
8
|
4
|
The
Adventures of Tin Tin
|
Animations
|
20
|
5
|
Lee
|
Pong
|
10
|
4
|
The
Adventures of Tin Tin
|
Animations
|
21
|
1
|
Adam
|
Smith
|
1
|
5
|
Safe
(2012)
|
Action
|
22
|
2
|
Ravi
|
Kumar
|
2
|
5
|
Safe
(2012)
|
Action
|
23
|
3
|
Susan
|
Davidson
|
5
|
5
|
Safe
(2012)
|
Action
|
24
|
4
|
Jenny
|
Adrianna
|
8
|
5
|
Safe
(2012)
|
Action
|
25
|
5
|
Lee
|
Pong
|
10
|
5
|
Safe
(2012)
|
Action
|
26
|
1
|
Adam
|
Smith
|
1
|
6
|
Safe
House(2012)
|
Action
|
27
|
2
|
Ravi
|
Kumar
|
2
|
6
|
Safe
House(2012)
|
Action
|
28
|
3
|
Susan
|
Davidson
|
5
|
6
|
Safe
House(2012)
|
Action
|
29
|
4
|
Jenny
|
Adrianna
|
8
|
6
|
Safe
House(2012)
|
Action
|
30
|
5
|
Lee
|
Pong
|
10
|
6
|
Safe
House(2012)
|
Action
|
31
|
1
|
Adam
|
Smith
|
1
|
7
|
GIA
|
18+
|
32
|
2
|
Ravi
|
Kumar
|
2
|
7
|
GIA
|
18+
|
33
|
3
|
Susan
|
Davidson
|
5
|
7
|
GIA
|
18+
|
34
|
4
|
Jenny
|
Adrianna
|
8
|
7
|
GIA
|
18+
|
35
|
5
|
Lee
|
Pong
|
10
|
7
|
GIA
|
18+
|
36
|
1
|
Adam
|
Smith
|
1
|
8
|
Deadline
2009
|
18+
|
37
|
2
|
Ravi
|
Kumar
|
2
|
8
|
Deadline
2009
|
18+
|
38
|
3
|
Susan
|
Davidson
|
5
|
8
|
Deadline
2009
|
18+
|
39
|
4
|
Jenny
|
Adrianna
|
8
|
8
|
Deadline
2009
|
18+
|
40
|
5
|
Lee
|
Pong
|
10
|
8
|
Deadline
2009
|
18+
|
41
|
1
|
Adam
|
Smith
|
1
|
9
|
The
Dirty Picture
|
18+
|
42
|
2
|
Ravi
|
Kumar
|
2
|
9
|
The
Dirty Picture
|
18+
|
43
|
3
|
Susan
|
Davidson
|
5
|
9
|
The
Dirty Picture
|
18+
|
44
|
4
|
Jenny
|
Adrianna
|
8
|
9
|
The
Dirty Picture
|
18+
|
45
|
5
|
Lee
|
Pong
|
10
|
9
|
The
Dirty Picture
|
18+
|
46
|
1
|
Adam
|
Smith
|
1
|
10
|
Marley
and me
|
Romance
|
47
|
2
|
Ravi
|
Kumar
|
2
|
10
|
Marley
and me
|
Romance
|
48
|
3
|
Susan
|
Davidson
|
5
|
10
|
Marley
and me
|
Romance
|
49
|
4
|
Jenny
|
Adrianna
|
8
|
10
|
Marley
and me
|
Romance
|
50
|
5
|
Lee
|
Pong
|
10
|
10
|
Marley
and me
|
Romance
|
Example 2:
There are three tables
involved:
1.The “C_PRODUCTS” table contains
the products master data that includes product id, product name, and sales
price.
2.The “C_STORES” table contains the stores where the products are sold.
3.The “C_SALES” table contains the products that sold in a particular
store by quantity and date.
Suppose we have three products
iPhone, iPad and Macbook Pro which are sold in two
stores North and South.
To create a Table: “C_PRODUCTS”
CREATE COLUMN TABLE "KABIL_PRACTICE"."C_PRODUCTS"
(
"ID" INT PRIMARY KEY generated by default as identity ,
"PRODUCT_NAME" VARCHAR(100),
"PRICE" DECIMAL(13 , 2 )
);
To insert a records into the “C_PRODUCTS” Table:
INSERT INTO "KABIL_PRACTICE"."C_PRODUCTS" ("PRODUCT_NAME", "PRICE") VALUES ('iPhone', 699);
INSERT INTO "KABIL_PRACTICE"."C_PRODUCTS" ("PRODUCT_NAME", "PRICE") VALUES ('iPad',599);
INSERT INTO "KABIL_PRACTICE"."C_PRODUCTS" ("PRODUCT_NAME", "PRICE") VALUES ('Macbook Pro',1299);
To create a Table: “C_STORES”
CREATE COLUMN TABLE "KABIL_PRACTICE"."C_STORES"
(
"ID" INT PRIMARY KEY generated by default as identity,
"STORE_NAME" VARCHAR(100)
);
To insert a records into the “C_STORES” Table:
INSERT INTO "KABIL_PRACTICE"."C_STORES"("STORE_NAME")VALUES('North');
INSERT
INTO "KABIL_PRACTICE"."C_STORES"("STORE_NAME")VALUES('South');
To create a Table: “C_SALES”
CREATE COLUMN TABLE "KABIL_PRACTICE"."C_SALES" (
"PRODUCT_ID" INT,
"STORE_ID" INT,
"QUANTITY" DECIMAL(13 , 2 ) NOT NULL,
"SALES_DATE" DATE NOT NULL,
PRIMARY KEY ("PRODUCT_ID", "STORE_ID"),
FOREIGN KEY ("PRODUCT_ID")
REFERENCES "KABIL_PRACTICE"."C_PRODUCTS" ("ID")
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("STORE_ID")
REFERENCES "KABIL_PRACTICE"."C_STORES" ("ID")
ON DELETE CASCADE ON UPDATE CASCADE
);
To insert a records into the “C_SALES” Table:
INSERT INTO "KABIL_PRACTICE"."C_SALES"("STORE_ID","PRODUCT_ID","QUANTITY","SALES_DATE")VALUES(1,1,20,'2017-01-02');
INSERT INTO "KABIL_PRACTICE"."C_SALES"("STORE_ID","PRODUCT_ID","QUANTITY","SALES_DATE")VALUES(1,2,15,'2017-01-05');
INSERT INTO "KABIL_PRACTICE"."C_SALES"("STORE_ID","PRODUCT_ID","QUANTITY","SALES_DATE")VALUES(1,3,25,'2017-01-05');
INSERT INTO "KABIL_PRACTICE"."C_SALES"("STORE_ID","PRODUCT_ID","QUANTITY","SALES_DATE")VALUES(2,1,30,'2017-01-02');
INSERT
INTO "KABIL_PRACTICE"."C_SALES"("STORE_ID","PRODUCT_ID","QUANTITY","SALES_DATE")VALUES(2,2,35,'2017-01-05');
To get the total sales
for each store and for each product, you calculate the sales and group them by
store and product as follows:
Code:
SELECT
"STORE_NAME",
"PRODUCT_NAME",
SUM("QUANTITY" * "PRICE") AS revenue
FROM
"KABIL_PRACTICE"."C_SALES"
INNER JOIN
"KABIL_PRACTICE"."C_PRODUCTS" ON "KABIL_PRACTICE"."C_PRODUCTS"."ID" = "KABIL_PRACTICE"."C_SALES"."PRODUCT_ID"
INNER JOIN
"KABIL_PRACTICE"."C_STORES" ON "KABIL_PRACTICE"."C_STORES"."ID" = "KABIL_PRACTICE"."C_SALES"."STORE_ID"
GROUP
BY "STORE_NAME"
, "PRODUCT_NAME";
RESULT:
Now, what if you want to
know also which store had no sales of a specific product. The query above could
not answer this question.
To solve the problem, you
need to use the CROSS JOIN clause.
First, you use the CROSS
JOIN clause to get the combination of all stores and products:
CODE:
SELECT
"STORE_NAME", "PRODUCT_NAME"
FROM
"KABIL_PRACTICE"."C_STORES" AS a
CROSS JOIN
"KABIL_PRACTICE"."C_PRODUCTS" AS b;
RESULT:
Next, you join the result
of the query above with the query that returns the total of sales by store and
by product. The following query illustrates the idea:
CODE:
SELECT
b."STORE_NAME",
a."PRODUCT_NAME",
IFNULL(c.revenue, 0) AS revenue
FROM
"KABIL_PRACTICE"."C_PRODUCTS" AS a
CROSS JOIN
"KABIL_PRACTICE"."C_STORES" AS b
LEFT JOIN
(SELECT
"KABIL_PRACTICE"."C_STORES"."ID" AS "STORE_ID",
"KABIL_PRACTICE"."C_PRODUCTS"."ID" AS "PRODUCT_ID",
"STORE_NAME",
"PRODUCT_NAME",
ROUND(SUM("QUANTITY" * "PRICE"), 0) AS revenue
FROM
"KABIL_PRACTICE"."C_SALES"
INNER JOIN "KABIL_PRACTICE"."C_PRODUCTS" ON "KABIL_PRACTICE"."C_PRODUCTS"."ID" = "KABIL_PRACTICE"."C_SALES"."PRODUCT_ID"
INNER JOIN "KABIL_PRACTICE"."C_STORES" ON "KABIL_PRACTICE"."C_STORES"."ID" = "KABIL_PRACTICE"."C_SALES"."STORE_ID"
GROUP BY "STORE_NAME" , "PRODUCT_NAME","KABIL_PRACTICE"."C_STORES"."ID","KABIL_PRACTICE"."C_PRODUCTS"."ID") AS c ON c."STORE_ID" = b."ID"
AND c."PRODUCT_ID"= a."ID"
ORDER
BY b."STORE_NAME";
RESULT:
