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:

Teachers Table:

Query:

SELECT * 
FROM students   
CROSS JOIN teachers;

Output:

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.