Oracle Cross Join

We will learn the usage of Cross Join in this tutorial for finding out the Cartesian product of two joined tables.

If one table contains ‘x’ number of rows and other table contains ‘y’ number of rows then, the final output will contain ( x * y ) number of rows.

Syntax:

The syntax of CROSS JOIN is as follows:

SELECT
    column_names
FROM
    Table_1
CROSS JOIN Table_2;

Here,

column_names – The columns we want in the final output.

Table_1 and Table_2 – The tables that we want to join using CROSS JOIN.

If we use CROSS JOIN for tables which are not related with each other, then we get a Cartesian product of rows and columns of both tables in the final output.

Oracle Cross Join Example

We will be using Employees and Departments table from HR schema for this example.

1. Simple CROSS JOIN Example

SELECT first_name, last_name, 
       department_name, salary
FROM employees 
CROSS JOIN departments ;

Result:

There are total 107 rows in Employee table and 27 rows in Departments table. CROSS JOIN of both table returns ( 107 * 27 ) = 2889 rows in total, where each row of Employees table is matched with each row from Departments table.

In this tutorial, we learned how to use CROSS JOIN for getting Cartesian product of joined tables.