SQL CROSS JOIN

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).

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;

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;

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)
);

CREATE TABLE sales_channel (
    channel_id INT PRIMARY KEY,
    channel VARCHAR (255)
);

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');

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');

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; 

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;

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;