Oracle CROSS JOIN

Summary: in this tutorial, you will learn how to use Oracle CROSS JOIN to combine each row from the first table with every row from the second table.

Introduction to Oracle CROSS JOIN clause #

A CROSS JOIN is an optional clause of a SELECT statement. A CROSS JOIN clause allows you to merge rows from two tables by combining each row from the first table with every row from the second table.

Here’s the syntax of the CROSS JOIN clause:

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

In this syntax, the CROSS JOIN clause combines each row in the X table with every row in the Y table to create a result set.

Unlike other joins such as LEFT JOIN, RIGHT JOIN, and FULL JOIN, the CROSS JOIN does not have a condition (ON clause) for joining rows of the two tables.

The result of a CROSS JOIN is known as a Cartesian product. In Math, given two sets X and Y, the Cartesian product of X x Y is the set of all ordered pair (x,y), where x belongs to X and y belongs to Y.

Visualizing an Oracle Cross Join #

Suppose you have two tables X and Y, and want to merge rows from both tables using a cross join:

• The X table has two columns: id (key) and x.
• The Y table has two columns: id (key) and y.

A cross join returns a result set that includes all possible combinations of rows from the X table with rows from the Y table:

The following diagram illustrates how a cross works:

Oracle CROSS JOIN Clause Example #

We’ll use the products and warehouses table from the sample database:

Suppose you want to carry a physical inventory, you can construct a query that returns the product name and warehouse name, and blank quantity.

The following statement uses a CROSS JOIN clause to combine each row of the products table with every row in the warehouses table:

SELECT
  warehouse_name,
  product_name,
  '' quantity
FROM
  products
  CROSS JOIN warehouses
ORDER BY
  warehouse_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

In this example, the cross join returns all combinations of product name and warehouses.

In practice, warehouse officers can use this query for physical inventory checking.

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

Summary #

• Use the Oracle CROSS JOIN to merge rows from two tables by combining a row from the first table with every row from the second table.