Cross join in Postgres

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

The CROSS JOIN, also referred to as the CARTESIAN JOIN, function in Postgres allows for joining each row in one table to all the rows of another table, creating a Cartesian product. A Cartesian Product is a factor of two sets to create a set of all of the ordered pairs with the first element of the ordered pair belonging to first set and second belonging the second set. For example, if set X is {doctor, patient} and set Y is {test, results} then X times Y will be {(doctor, test), (patient, results), (doctor, results), (patient, test)}. This tutorial will explain how to use a cross join in Postgres to create a Cartesian product of database rows in the joined tables.

Prerequisite

  • Postgres must be properly installed and configured on the local system in order to perform a cross join in Postgres.

What is CROSS JOIN

The Postgres CROSS JOIN function allows for the creation of a Cartesian Product, or a product of two sets from two or more tables. The CROSS join function does not require any condition be specified in the join clause, as it does with the LEFT JOIN operator, that must be matched.

For example, assume a scenario where a CROSS JOIN operation is being performed between two tables named ‘customer’ and ‘sales’, were each row from the “customer” and “sales” tables will result in a Cartesian product. The new set will be composed of a row that consists of all fields or columns in the customer table combined with all of the fields in the sales table.

Following is the basic form of the CROSS JOIN command:

1
2
3
SELECT *
FROM customer
CROSS JOIN sales;

The above statement will produce results that resemble the following:

1
2
SELECT *
FROM customer, sales;

Create Sample Dataset

This section will explain how to create a sample dataset that will be used in the examples in this tutorial.

First, create the customer table by executing the following ‘CREATE TABLE’ statement:

Table X

1
CREATE TABLE customer (id VARCHAR(20) PRIMARY KEY, name VARCHAR(50));

Next, create the sales table by executing the same ‘CREATE TABLE’ statement, but having different fields or columns, with the following command:

Table y

1
CREATE TABLE sales (sales_id INT PRIMARY KEY, customer_no VARCHAR(10), product_no VARCHAR (20), qty_sold INT);

Now insert some sample records into the tables as follows:

1
2
3
4
5
6
INSERT INTO customer (id, name)
VALUES
   ('C01','James'),
   ('C02','Keren'),
   ('C03','Connor'),
   ('C04','Risa');
1
2
3
4
5
6
7
INSERT INTO sales (sales_id, customer_no, product_no, qty_sold)
VALUES
   (1,'C01','12345C',21),
   (2,'C02','12345D',32),
   (3,'C03','12345E',54),
   (4,'C04','12345F',12),
   (5,'C01','12345G',89);

CROSS JOIN Example

The newly created two-table sample dataset will now be used to demonstrate how to use the CROSS JOIN function on the two tables. Execute the following CROSS JOIN command:

1
2
3
SELECT * FROM customer
CROSS JOIN
sales;

The output should resemble the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 id  |  name  | sales_id | customer_no | product_no | qty_sold
-----+--------+----------+-------------+------------+----------
 C01 | James  |        1 | C01         | 12345C     |       21
 C02 | Keren  |        1 | C01         | 12345C     |       21
 C03 | Connor |        1 | C01         | 12345C     |       21
 C04 | Risa   |        1 | C01         | 12345C     |       21
 C01 | James  |        2 | C02         | 12345D     |       32
 C02 | Keren  |        2 | C02         | 12345D     |       32
 C03 | Connor |        2 | C02         | 12345D     |       32
 C04 | Risa   |        2 | C02         | 12345D     |       32
 C01 | James  |        3 | C03         | 12345E     |       54
 C02 | Keren  |        3 | C03         | 12345E     |       54
 C03 | Connor |        3 | C03         | 12345E     |       54
 C04 | Risa   |        3 | C03         | 12345E     |       54
 C01 | James  |        4 | C04         | 12345F     |       12
 C02 | Keren  |        4 | C04         | 12345F     |       12
 C03 | Connor |        4 | C04         | 12345F     |       12
 C04 | Risa   |        4 | C04         | 12345F     |       12
 C01 | James  |        5 | C01         | 12345G     |       89
 C02 | Keren  |        5 | C01         | 12345G     |       89
 C03 | Connor |        5 | C01         | 12345G     |       89
 C04 | Risa   |        5 | C01         | 12345G     |       89

The same result can also be achieved by executing the following statement:

1
 SELECT * FROM customer, sales;

Alternatively, the field can be specified as follows during the SELECT operations:

1
2
 SELECT customer.id, customer.name, sales.sales_id, sales.customer_no, sales.product_no, sales.qty_sold
 FROM customer, sales;

The results should resemble the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 id  |  name  | sales_id | customer_no | product_no | qty_sold
-----+--------+----------+-------------+------------+----------
 C01 | James  |        1 | C01         | 12345C     |       21
 C02 | Keren  |        1 | C01         | 12345C     |       21
 C03 | Connor |        1 | C01         | 12345C     |       21
 C04 | Risa   |        1 | C01         | 12345C     |       21
 C01 | James  |        2 | C02         | 12345D     |       32
 C02 | Keren  |        2 | C02         | 12345D     |       32
 C03 | Connor |        2 | C02         | 12345D     |       32
 C04 | Risa   |        2 | C02         | 12345D     |       32
 C01 | James  |        3 | C03         | 12345E     |       54
 C02 | Keren  |        3 | C03         | 12345E     |       54
 C03 | Connor |        3 | C03         | 12345E     |       54
 C04 | Risa   |        3 | C03         | 12345E     |       54
 C01 | James  |        4 | C04         | 12345F     |       12
 C02 | Keren  |        4 | C04         | 12345F     |       12
 C03 | Connor |        4 | C04         | 12345F     |       12
 C04 | Risa   |        4 | C04         | 12345F     |       12
 C01 | James  |        5 | C01         | 12345G     |       89
 C02 | Keren  |        5 | C01         | 12345G     |       89
 C03 | Connor |        5 | C01         | 12345G     |       89
 C04 | Risa   |        5 | C01         | 12345G     |       89
(20 rows)

Conclusion

This tutorial covered how to use the cross join in Postgres to create a Cartesian product. The article specifically explained what a CROSS JOIN function is and provided the basic form of the CROSS JOIN command. The tutorial also provided instructions for creating a sample dataset of two tables, how to insert sample records into the tables and then provided a CROSS JOIN example. Remember that, unlike the LEFT JOIN function, the CROSS join function does not require a matching condition be specified in the join clause.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.