Introduction to the PostgreSQL CROSS JOIN clause

In PostgreSQL, a cross-join allows you to join two tables by combining each row from the first table with every row from the second table, resulting in a complete combination of all rows.

In the set theory, we can say that a cross-join produces thecartesian product of rows in two tables.

Unlike otherjoin clauses such asLEFT JOIN  orINNER JOIN, theCROSS JOIN clause does not have a join predicate.

Suppose you have to perform aCROSS JOIN oftable1 andtable2.

Iftable1 hasn rows andtable2 hasm rows, theCROSS JOIN will return a result set that hasnxm rows.

For example, thetable1 has1,000 rows andtable2 has1,000 rows, the result set will have1,000 x 1,000 =1,000,000 rows.

Because aCROSS JOIN may generate a large result set, you should use it carefully to avoid performance issues.

Here’s the basic syntax of theCROSS JOIN syntax:

SELECT  select_listFROM  table1CROSS JOIN table2;

The following statement is equivalent to the above statement:

SELECT  select_listFROM  table1,table2;

Alternatively, you can use anINNER JOIN clause with a condition that always evaluates to true to simulate a cross-join:

SELECT  select_listFROM  table1  INNER JOIN table2ON true;

PostgreSQL CROSS JOIN example

The followingCREATE TABLE statements createT1 andT2 tables andinsert sample data for the cross-join demonstration.

DROP TABLE IF EXISTS T1;CREATE TABLE  T1 (LABELCHAR(1)PRIMARY KEY);DROP TABLE IF EXISTS T2;CREATE TABLE  T2 (scoreINT PRIMARY KEY);INSERT INTO  T1 (LABEL)VALUES  ('A'),  ('B');INSERT INTO  T2 (score)VALUES  (1),  (2),  (3);

The following statement uses theCROSS JOIN operator to joinT1 table withT2 table:

SELECT *FROM T1CROSS JOIN T2;

label | score-------+------- A     |     1 B     |     1 A     |     2 B     |     2 A     |     3 B     |     3(6 rows)

The following picture illustrates how theCROSS JOIN works when joining theT1 table with theT2 table:

Some practical examples of using CROSS JOIN

In practice, you can find theCROSS JOIN useful when you need to combine data from two tables without specific matching conditions. For example:

1) Scheduling

Suppose you have a table foremployees andshifts, and you want to create a schedule that lists all possible combinations of employees and shifts to explore various staffing scenarios:

SELECT *FROM employeesCROSS JOIN shift;

2) Inventory management

In an inventory management system, you have tables forwarehouses andproducts. ACROSS JOIN can help you analyze the availability of each product in every warehouse:

SELECT *FROM productsCROSS JOIN warehouses;

Summary

• Use the PostgreSQLCROSS JOIN clause to make a cartesian product of rows in two tables.

Last updated onApril 8, 2024

On this page

• Introduction to the PostgreSQL CROSS JOIN clause
• PostgreSQL CROSS JOIN example
• Some practical examples of using CROSS JOIN
• Summary