Oracle CROSS JOIN

Summary: in this tutorial, you will learn how to use Oracle CROSS JOIN to make a Cartesian product of the joined tables.

Introduction to Oracle CROSS JOIN clause

In Mathematics, given two sets A and B, the Cartesian product of A x B is the set of all ordered pair (a,b), whicha belongs to A and b belongs to B.

To create a Cartesian product of tables in Oracle, you use the CROSS JOIN clause. The following illustrates the syntax of the CROSS JOIN clause:

SELECT
    column_list
FROM
    T1 
CROSS JOIN T2; 
Code language: SQL (Structured Query Language) (sql)

Unlike other joins such as INNER JOIN or LEFT JOIN, CROSS JOIN does not have the ON clause with a join predicate.

When you perform a cross join of two tables, which have no relationship, you will get a Cartesian product of rows and columns of both tables.

The cross join is useful when you want to generate plenty of rows for testing. Suppose we have two tables that have m and n rows, the Cartesian product of these tables has m x n rows.

Oracle Cross Join Example

See the following inventories table in the sample database.

Each row in the inventories table requires data for product_id, warehouse_id, and quantity.

To generate the test data for insertion into the inventories table, you can use the CROSS JOIN clause as shown in the following statement:

SELECT
    product_id,
    warehouse_id,
    ROUND( dbms_random.value( 10, 100 )) quantity
FROM
    products 
CROSS JOIN warehouses;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example, the cross join made a Cartesian of product_id and warehouse_id from the products and warehouses tables.

The products table 288 rows and the warehouses table has 9 rows, therefore, the cross join of these tables returns 2592 rows (288 x 9).

Note that we used the dbms_random.value() function to get a random number between 10 and 100, and ROUND() function to get the integer values from the random number.

In this tutorial, you have learned how to use the Oracle CROSS JOIN to make a Cartesian product of joined tables.