SQL CROSS JOIN

Summary: this tutorial shows you how to use the SQL CROSS JOIN to make a Cartesian product of the joined tables.

Introduction to SQL CROSS JOIN clause

A cross join is a join operation that produces the Cartesian product of two or more tables.

In Math, a Cartesian product is a mathematical operation that returns a product set of multiple sets.

For example, with two sets A {x,y,z} and B {1,2,3}, the Cartesian product of A x B is the set of all ordered pairs (x,1), (x,2), (x,3), (y,1) (y,2), (y,3), (z,1), (z,2), (z,3).

The following picture illustrates the Cartesian product of A and B:

Similarly, in SQL, a Cartesian product of two tables A and B is a result set in which each row in the first table (A) is paired with each row in the second table (B). Suppose the A table has n rows and the B table has m rows, the result of the cross join of the A and B tables have n x m rows.

The following illustrates syntax of the CROSS JOIN clause:

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

The following picture illustrates the result of the cross join between the table A and table B. In this illustration, the table A has three rows 1, 2 and 3 and the table B also has three rows x, y and z. As the result, the Cartesian product has nine rows:

Note that unlike the INNER JOIN, LEFT JOIN, and FULL OUTER JOIN, the CROSS JOIN clause does not have a join condition.

The following statement is equivalent to the one that uses the CROSS JOIN clause above:

SELECT 
    column_list
FROM
    A,
    B;Code language: SQL (Structured Query Language) (sql)

SQL CROSS JOIN example

We will create two new tables  for the demonstration of the cross join:

•  sales_organization table stores the sale organizations.
•  sales_channel table stores the sales channels.

The following statements create the sales_organization and sales_channel tables:

CREATE TABLE sales_organization (
	sales_org_id INT PRIMARY KEY,
	sales_org VARCHAR (255)
);Code language: SQL (Structured Query Language) (sql)

CREATE TABLE sales_channel (
	channel_id INT PRIMARY KEY,
	channel VARCHAR (255)
);Code language: SQL (Structured Query Language) (sql)

Suppose the company has two sales organizations that are Domestic and Export, which are in charge of sales in the domestic and international markets.

The following statement inserts two sales organizations into the sales_organization table:

INSERT INTO sales_organization (sales_org_id, sales_org)
VALUES
	(1, 'Domestic'),
	(2, 'Export');Code language: SQL (Structured Query Language) (sql)

The company can distribute goods via various channels such as wholesale, retail, eCommerce, and TV shopping. The following statement inserts sales channels into the sales_channel table:

INSERT INTO sales_channel (channel_id, channel)
VALUES
	(1, 'Wholesale'),
	(2, 'Retail'),
	(3, 'eCommerce'),
	(4, 'TV Shopping');Code language: SQL (Structured Query Language) (sql)

To find the all possible sales channels that a sales organization can have, you use the CROSS JOIN to join the sales_organization table with the sales_channel table as follows:

SELECT
	sales_org,
	channel
FROM
	sales_organization
CROSS JOIN sales_channel; 
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

The result set includes all possible rows in the sales_organization and sales_channel tables.

The following query is equivalent to the statement that uses the CROSS JOIN clause above:

SELECT
	sales_org,
	channel
FROM
	sales_organization,
	sales_channel;Code language: SQL (Structured Query Language) (sql)

In some database systems such as PostgreSQL and Oracle, you can use the INNER JOIN clause with the condition that always evaluates to true to perform a cross join such as:

SELECT
	sales_org,
	channel
FROM
	sales_organization
INNER JOIN sales_channel ON 1 = 1;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL CROSS JOIN clause to produce a Cartesian product of two or more tables.