What Is a PostgreSQL Cross Join?

Abstract shapes over a black background.

In the SQL query language, joins are a powerful feature that allows developers to combine rows from two or more tables based on a related column. Among various types of joins—inner join, left join, right join, full outer join, and cross join—the cross join, also known as Cartesian join, holds a unique position.

A cross join in PostgreSQL returns the Cartesian product of rows from the tables in the join. In other words, it combines each row from the first table with each row from the second table.

Understanding the Syntax of PostgreSQL Cross Join

The basic syntax for a cross join in PostgreSQL is as follows:

SELECT column_name(s) FROM table1 CROSS JOIN table2;

Here, table1 and table2 are the tables you wish to join.

Example of Cross Join Usage in PostgreSQL

Let's dive into an example that illustrates how cross joins work in PostgreSQL:

CREATE TABLE colors ( color_id INT PRIMARY KEY, color_name VARCHAR(255));

CREATE TABLE sizes ( size_id INT PRIMARY KEY, size_name VARCHAR(255));

INSERT INTO colors VALUES (1, 'Red'), (2, 'Blue'), (3, 'Green');

INSERT INTO sizes VALUES (1, 'Small'), (2, 'Medium'), (3, 'Large');

SELECT colors.color_name, sizes.size_name FROM colors CROSS JOIN sizes;

This query will return all combinations of colors and sizes, which is useful for generating all product variations in an e-commerce database.

seo=# SELECT colors.color_name, sizes.size_name seo-# FROM colors seo-# CROSS JOIN sizes; color_name | size_name  ------------+----------- Red        | Small Red        | Medium Red        | Large Blue       | Small Blue       | Medium Blue       | Large Green      | Small Green      | Medium Green      | Large

Real-World Use Cases of Cross Joins

Cross joins can be highly beneficial in specific scenarios:

  • E-commerce platforms: Generating all possible combinations of product attributes such as color, size, and style.

  • Planning and scheduling applications: Creating all possible pairs of entities such as tasks and resources, or events and timeslots.

Visualizing Cross Joins

Visualize two lists, each representing a table. A cross join results in a matrix where each element from the first list (table) is paired with each element from the second list (table).

SQL cross join diagram

Conclusion

Mastering cross joins in PostgreSQL allows developers to generate comprehensive combinations of data efficiently. While not as commonly used as other join types, understanding and using cross joins can significantly enhance your data manipulation capabilities in specific scenarios. 

Learn More About Join Strategies to Enhance Your Database Performance

Understanding how the PostgreSQL parser picks a join method can be valuable to define join strategies to enhance your database performance. To learn more about this topic and take your knowledge of joins to another level, check out this article on PostgreSQL Join Type Theory.