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 the syntax of the CROSS JOIN
clause:
SELECT column_list
FROM A
CROSS JOIN B;
The following picture illustrates the result of the cross join between table A and table B. In this illustration, table A has three rows 1, 2, and 3 and 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
the 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
the table stores the sale organizations. -
sales_channel
the 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 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;
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;
In some database systems such as PostgreSQL and Oracle, you can use the INNER JOIN
the 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;
In this tutorial, you have learned how to use the SQL CROSS JOIN clause to produce a Cartesian product of two or more tables.
0 Comments
CAN FEEDBACK
Emoji