CROSS JOIN behavior in PostgreSQL

Introduction

A CROSS JOIN combines every row from one table with every row from another table. It helps to see all possible pairs between two sets of data.

When you want to pair each product with every store to check availability.

When creating all possible combinations of options, like colors and sizes.

When you need to generate test data by combining two lists.

When analyzing every possible match between two groups, like students and projects.

Syntax

PostgreSQL

SELECT * FROM table1 CROSS JOIN table2;

The result has rows equal to the number of rows in table1 multiplied by the number of rows in table2.

You can also write CROSS JOIN simply by listing tables separated by commas, but using CROSS JOIN is clearer.

Examples

This returns every color combined with every size.

PostgreSQL

SELECT * FROM colors CROSS JOIN sizes;

This pairs each product with every store location.

PostgreSQL

SELECT products.name, stores.location FROM products CROSS JOIN stores;

This is an older way to write a CROSS JOIN, but it works the same.

PostgreSQL

SELECT * FROM table1, table2;

Sample Program

This example creates two small tables, colors and sizes, then uses CROSS JOIN to list all color-size pairs.

PostgreSQL

CREATE TEMP TABLE colors (color TEXT);
INSERT INTO colors VALUES ('Red'), ('Blue');

CREATE TEMP TABLE sizes (size TEXT);
INSERT INTO sizes VALUES ('Small'), ('Large');

SELECT color, size FROM colors CROSS JOIN sizes ORDER BY color, size;

Important Notes

CROSS JOIN can create very large results if tables have many rows, so use it carefully.

It does not require any matching columns like INNER JOIN does.

Summary

CROSS JOIN pairs every row from the first table with every row from the second.

It is useful to create all possible combinations between two sets of data.

Be careful with large tables because the result size grows quickly.