PostgreSQL Cross Join By Example

PostgreSQL `CROSS JOIN` by Example

A CROSS JOIN in PostgreSQL returns the Cartesian product of the two tables involved. That means it combines every row from the first table with every row from the second table. The result contains all possible combinations of rows.

Key Characteristics of `CROSS JOIN`:

It does not require a condition to join the tables.
It produces a result where each row from the first table is paired with all rows from the second table.
The number of rows in the result will be the product of the number of rows in each table (m * n where m is the number of rows in the first table, and n is the number of rows in the second table).

1. Syntax of `CROSS JOIN`

SELECT columns
FROM table1
CROSS JOIN table2;

2. Example 1: Simple `CROSS JOIN`

Scenario:

Consider two tables:

customers table with 2 rows: customer_id, first_name.
products table with 3 rows: product_id, product_name.

`customers` Table:

customer_id	first_name
1	John
2	Jane

`products` Table:

product_id	product_name
101	Laptop
102	Phone
103	Tablet

Query: Get All Possible Combinations of Customers and Products

SELECT customers.first_name, products.product_name
FROM customers
CROSS JOIN products;

Result:

first_name	product_name
John	Laptop
John	Phone
John	Tablet
Jane	Laptop
Jane	Phone
Jane	Tablet

Explanation: Every customer is paired with every product, so the result has 2 customers × 3 products = 6 rows.

3. Example 2: Using `CROSS JOIN` for Combinations of Dates and Products

Let’s say we have a dates table with the following values:

`dates` Table:

date
2025-01-01
2025-01-02

We also have the products table from the previous example.

Query: Get All Combinations of Dates and Products

SELECT dates.date, products.product_name
FROM dates
CROSS JOIN products;

Result:

date	product_name
2025-01-01	Laptop
2025-01-01	Phone
2025-01-01	Tablet
2025-01-02	Laptop
2025-01-02	Phone
2025-01-02	Tablet

Explanation: The query returns a Cartesian product of the 2 dates with the 3 products, resulting in 6 combinations.

4. Example 3: Creating a Price List for Every Customer and Product

Let’s imagine that we have a prices table, which contains the price for each product.

`prices` Table:

product_id	price
101	1000
102	500
103	300

We can now combine the customers, products, and prices tables using CROSS JOIN to get all possible price combinations for each customer and product.

Query: Get All Possible Price Combinations for Customers and Products

SELECT customers.first_name, products.product_name, prices.price
FROM customers
CROSS JOIN products
CROSS JOIN prices;

Result:

first_name	product_name	price
John	Laptop	1000
John	Laptop	500
John	Laptop	300
John	Phone	1000
John	Phone	500
John	Phone	300
John	Tablet	1000
John	Tablet	500
John	Tablet	300
Jane	Laptop	1000
Jane	Laptop	500
Jane	Laptop	300
Jane	Phone	1000
Jane	Phone	500
Jane	Phone	300
Jane	Tablet	1000
Jane	Tablet	500
Jane	Tablet	300

Explanation: This query produces a Cartesian product of customers, products, and prices, resulting in 18 rows (2 customers × 3 products × 3 prices).

5. Performance Considerations

Size of Result Set: CROSS JOIN can generate very large result sets, especially when joining large tables. Always be cautious when performing a CROSS JOIN on tables with many rows, as the result set can quickly become massive.
Avoid Unintended Cartesian Products: CROSS JOIN might create more rows than expected. It's important to ensure that this is the desired behavior and that it doesn't result in unwanted performance issues.

Summary of `CROSS JOIN`

Operation	Description
Purpose	Returns the Cartesian product of two or more tables.
Result	Each row from the first table is paired with each row from the second table.
Performance	Can produce large result sets, so be cautious when using large tables.
Use Case	Useful for generating combinations, for example, for a price list, schedules, etc.

Would you like to explore more complex scenarios or use cases for CROSS JOIN? 🚀

Souy Soeng

PostgreSQL Cross Join By Example