CROSS JOIN in Oracle

CROSS JOIN
The Oracle CROSS Join query joins all the rows of one table with all the rows of another table and then displays the result. For instance, if the FIRST table has x rows and the Second Table has y rows than the resultant table will have x*y rows. Thus this query is often called as the cartesian products or the cartesian join. It is not mandatory to mention the CROSS JOIN keyword for this type of joining to take place. It also happens when there are two tables in the join query with no condition of joining, then by default the joining will be of CROSS join type and thus result obtained will be a cartesian product of the two tables.

Syntax 1: Using CROSS JOIN Keyword.

SELECT * 
FROM table_1   
CROSS JOIN table_2;

Syntax 2: Without using CROSS JOIN Keyword.

SELECT * 
FROM table_1, table_2;

Parameters:
table_1, table_2: It is used to specify the name of the tables from which the records need to be joined.

Example: Using CROSS JOIN Keyword.

Students Table:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 20
2 Smiley 19
3 Happy 21
4 James 22
5 Bond 25

Teachers Table:

TEACHER_ID TEACHER_NAME TEACHER_AGE
101 James 30
102 Bond 25
103 Smith 40

Query:

SELECT * 
FROM students   
CROSS JOIN teachers;

Output:

 

STUDENT_ID STUDENT_NAME STUDENT_AGE TEACHER_ID TEACHER_NAME TEACHER_AGE
1 Joy 20 101 James 30
2 Smiley 19 101 James 30
3 Happy 21 101 James 30
4 James 22 101 James 30
5 Bond 25 101 James 30
1 Joy 20 102 Bond 25
2 Smiley 19 102 Bond 25
3 Happy 21 102 Bond 25
4 James 22 102 Bond 25
5 Bond 25 102 Bond 25
1 Joy 20 103 Smith 40
2 Smiley 19 103 Smith 40
3 Happy 21 103 Smith 40
4 James 22 103 Smith 40
5 Bond 25 103 Smith 40

Explanation:
The ‘students’ and the ‘teachers’ are the already existing tables with 5 and 3 rows respectively. After the joining, there will be 5*3 = 15 rows displayed in the table.

Please Share