PostgreSQL Cross Join

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:

PostgreSQL CROSS JOIN illustration

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.
Was this tutorial helpful ?