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
B, the Cartesian product of
A x B is the set of all ordered pair (a,b), which
a belongs to
b belongs to
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;
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
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
To generate the test data for inserting 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;
Here is the output:
In this example, the cross join made a Cartesian of
warehouse_id from the
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. 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.