Summary: in this tutorial, you will learn how to use the PostgreSQL CROSS JOIN
to produce a cartesian product of rows from the joined tables.
Introduction to the PostgreSQL CROSS JOIN clause
A CROSS JOIN
allows you to produce a cartesian product of rows in two tables. It means that the CROSS JOIN
combines each row from the first table with every row from the second table, resulting in a complete combination of all rows.
Different from other join clauses such as LEFT JOIN or INNER JOIN, the CROSS JOIN
clause does not have a join predicate.
Suppose you have to perform a CROSS JOIN
of table1
and table2
.
If table1
has n
rows and table2
has m
rows, the CROSS JOIN will return a result set that has nxm
rows.
For example, the table1
has 1,000
rows and table2
has 1,000
rows, the result set will have 1,000 x 1,000
= 1,000,000
rows.
Because a CROSS JOIN
may generate a large result set, you should use it judiciously to avoid performance issues.
Here’s the basic syntax of the CROSS JOIN
syntax:
SELECT
select_list
FROM
table1
CROSS JOIN table2;
Code language: SQL (Structured Query Language) (sql)
The following statement is equivalent to the above statement:
SELECT
select_list
FROM
table1,table2;
Code language: SQL (Structured Query Language) (sql)
Alternatively, you can use an INNER JOIN
clause with a condition that always evaluates to true to simulate the cross-join:
SELECT
select_list
FROM
table1
INNER JOIN table2 ON true;
Code language: SQL (Structured Query Language) (sql)
PostgreSQL CROSS JOIN example
The following CREATE TABLE statements create T1 and T2 tables and insert sample data for the cross-demonstration.
DROP TABLE IF EXISTS T1;
CREATE TABLE T1 (label CHAR(1) PRIMARY KEY);
DROP TABLE IF EXISTS T2;
CREATE TABLE T2 (score INT PRIMARY KEY);
INSERT INTO T1 (label)
VALUES
('A'),
('B');
INSERT INTO T2 (score)
VALUES
(1),
(2),
(3);
Code language: SQL (Structured Query Language) (sql)
The following statement uses the CROSS JOIN
operator to join table T1 with table T2.
SELECT *
FROM T1
CROSS JOIN T2;
Code language: SQL (Structured Query Language) (sql)
label | score
-------+-------
A | 1
B | 1
A | 2
B | 2
A | 3
B | 3
(6 rows)
The following picture illustrates the result of the CROSS JOIN
when joining the table T1 to the table T2:
Some practical examples of using CROSS JOIN
In practice, you can find the CROSS JOIN
useful when you need to combine data from two tables without specific matching conditions. For example:
1) Scheduling
Suppose you have a table for employees and shifts, and you want to create a schedule that lists all possible combinations of employees and shifts to explore various staffing scenarios:
SELECT *
FROM employee
CROOS JOIN shift;
2) Inventory management
In an inventory management system, you have tables for warehouses and products. A CROSS JOIN
can help you analyze the availability of each product in every warehouse:
SELECT *
FROM products
CROSS JOIN warehouses;
Summary
- Use the PostgreSQL
CROSS JOIN
clause to make a cartesian product of rows in two tables.