Oracle: Cross Join

Since Childhood, We all handle multiplicative calculations in every phase of our lives. We all know how to perform simple multiplications. When we need to multiply two SETS, we call the result a Cartesian product or the Cross Product.

For example, We have 2 set:

A = {a,b,c}

B = {1,2}

So the cartesian product of A and B is represented by AXB.

and the result will be:

AXB = {(a,1), (a,2), (b,1), (b,2), (c,1), (c,2)}

NOTE

1. The result of AXB will not be equal to the output of BXA.
2. The total rows that appear as a result of AXB will be: Number of row in Set A * Number of row in Set B

Therefore, CROSS JOIN in Oracle helps to find out the Cartesian product of 2 Tables. It can be helpful while handling multiple sets of data while working on large business projects. To understand the concept refer to the below diagram:

Syntax

select * from Table1 CROSS JOIN Table2;

Example

We have two tables. The Stu table is as follows:

The Children table is given below:

Now we want to apply CROSS JOIN between both the tables:

select * from Stu CROSS JOIN Children;

Output