Master PostgreSQL Cross Join: Examples & Use Cases

A CROSS JOIN in PostgreSQL combines every row from one table with every row from another table, creating a Cartesian product. This is useful for generating combinations, creating test data, or pairing elements from two datasets.

Basic Syntax

SELECT column1, column2
FROM table1
CROSS JOIN table2;

You can also use the implicit syntax:

SELECT column1, column2
FROM table1, table2;

Example: Product and Color Combinations

Let’s consider two simple tables, products and colors, to demonstrate how a CROSS JOIN works.

products table:

colors table:

To generate all possible combinations of products and colors:

SELECT product_name, color_name
FROM products
CROSS JOIN colors;

Expected Output:

The CROSS JOIN combines every product with every color, yielding a total of 2 products × 2 colors = 4 combinations.

Common Use Cases

Generating all combinations: Create product variants by combining sizes and colors:

SELECT size, color
FROM sizes CROSS JOIN colors;

Creating test data: Generate sample data for testing by combining different parameters:

SELECT strategy, scenario
FROM strategies CROSS JOIN scenarios;

Report templates: Create placeholder rows for all possible combinations in reports.

Important Considerations

Performance: CROSS JOIN can generate very large result sets. A table with 1,000 rows crossed with another 1,000-row table produces 1,000,000 rows.

Filtering: Use WHERE clauses to limit results:

SELECT product_name, color_name
FROM products CROSS JOIN colors
WHERE product_name = 'T-Shirt';

Conclusion

CROSS JOIN is useful for generating combinations and test data, but use it carefully with large tables to avoid performance issues. Always consider the size of your result set before running the query.